Vlookup and Match

ExcelVlookup · #1 06-19-2015, 07:10 AM

Hello,

I have two sheets and I want to look up information in sheet two and paste it than automatically if it fullfills the conditions in sheet one.
The conditions are that the name is the same and the date. So for example
look up the name "MAXIMUS - TOT RETURN IND" and the date "08.07.1997" in sheet 2 and then paste the stock price in the corresponding cell in sheet 1.

I attached the file. It would be very nice if someone could help me.
Thank you very much!

beginner · #2 06-19-2015, 10:07 AM

Quote:

Originally Posted by ExcelVlookup

It would be very nice if someone could help me.

If I understand you, try this formula

On Sheet1 in to cell B3 put this formula and copy to right and to down to last row

Code:

=IFERROR(VLOOKUP($A3;Sheet2!$A$2:$F$8544;MATCH(B$1;Sheet2!$A$1:$F$1;0);0);"")

beginner · #3 06-19-2015, 10:24 AM

Or try

In to cell B3

Code:

=IFERROR(VLOOKUP($A3;Sheet2!$A$2:$F$8544;MATCH(B$1;Sheet2!$A$1:$F$1;0);0);"")

In to cell C3

Code:

=IFERROR(VLOOKUP($A3;Sheet2!$C$2:$F$8544;MATCH(C$1;Sheet2!$C$1:$F$1;0);0);"")

In to cell D3

Code:

=IFERROR(VLOOKUP($A3;Sheet2!$E$2:$F$8544;MATCH(D$1;Sheet2!$E$1:$F$1;0);0);"")

etc

beginner · #4 06-19-2015, 01:12 PM

If you have more columns, you can try this formulas

In to cell B3 and copy down (If you want quickly copying formula down, double click mouse to Fill handle)

Code:

=IFERROR(VLOOKUP($A3;INDIRECT("Sheet2!"&SUBSTITUTE(ADDRESS(2;COLUMN(A1);1);"0";"")&":$H$8544");MATCH(B$1;Sheet2!$A$1:$F$1;0);0);"")

In to cell C3 copy to right and copy all down

Code:

=IFERROR(VLOOKUP($A3;INDIRECT("Sheet2!"&SUBSTITUTE(ADDRESS(2;COLUMN(B1)+COLUMN(A1);1);"0";"")&":$H$8544");MATCH(C$1;INDIRECT("Sheet2!"&SUBSTITUTE(ADDRESS(1;COLUMN(A1)+COLUMN(B1);1);"0";"")&":$H$1");0);0);"")

Please see attach

ExcelVlookup · #5 06-22-2015, 01:42 AM

Thank you very much! this helps

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
vlookup or match	uhlersa	Excel	2	12-04-2012 12:35 AM
Match Index with sumproduct/vlookup	angie.chang	Excel	1	06-18-2012 08:47 AM
Vlookup or Index/Match - Multiple Criteria	ruci1225	Excel	1	01-15-2012 07:31 AM
Vlookup, offset, match & countif	jujuwillis	Excel	2	10-15-2011 11:06 AM
Vlookup and If conditions together along with match formulas	david_benjamin	Excel	2	04-11-2011 11:30 AM