MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Exact Value return


Posted by Martin Pingree on August 09, 2001 10:34 AM

Sheet 1 contains
4
5
6
Sheet 2 contains
1 A
3 B
5 C
7 D
9 E

I'm trying to use LOOKUP on sheet 1 to get an exact match from sheet 2. The LOOKUP formula of=LOOKUP(A1,Sheet2!A:A,Sheet2!B:B) for A1 thru A3 obviously doesn't find a match but puts the values of b,c,d accordingly. Is there a way to use INDEX or MATCH to accomplish this as LOOKUP doesn't appear that it will work ?


Posted by Barrie Davidson on August 09, 2001 10:39 AM

You should use VLOOKUP. Try this instead,
=VLOOKUP(A1,Sheet2!A:B,2,0)

Barrie

Posted by Martin Pingree on August 09, 2001 10:43 AM

Thanks Barrie, but what if the lookup value is not in the first column or row ?

Posted by Barrie Davidson on August 09, 2001 10:51 AM

Just change the reference to the lookup value's address. For example, if you are looking up a value that you have in cell C3, your formula would be:

=VLOOKUP(C3,Sheet2!A:B,2,0)

Barrie

Posted by Martin Pingree on August 09, 2001 11:09 AM

Many many thanks !!