VLOOKUP based on multiple lookup_values?
Posted by david m on January 28, 2002 7:16 PM
Is there a way to do a VLOOKUP that is based on more than one lookup_value or criteria? For example, assume the following on Sheet 1: the value 1 is in cells A1, A2, and A3 a,b, and c are the values in B1, B2, and B3 the value 2 is in cells C1, C2, and C3 xxx, yyy, and zzz are the values in cells D1, D2, and D3. Is there a formula I can put on Sheet 2 that would examine data entry in three different cells on Sheet 2, look for a match in a row on Sheet 1 and return the value from the 4th column (i.e. if I enter 1, B, 2 in three cells could a formula return yyy?).
Posted by Stephen Hoadley on January 28, 2002 7:35 PM
in a spare column join all columns togeather using formula =A1&" "&B1&" "&C1 then do a vlookup on this column
hope this helps
Posted by Cunegonde on January 29, 2002 4:57 AM
Stephen's suggestion is probably better (simpler), but it could also be done with this array formula :-
=INDEX(Sheet1!D1:D10,MATCH(A1&B1&C1,Sheet1!A1:A100&Sheet1!B1:B100&Sheet1!C1:C100,0))
Posted by Cunegonde on January 29, 2002 4:59 AM
Correction ....
Should read :-
=INDEX(Sheet1!D1:D100,MATCH(A1&B1&C1,Sheet1!A1:A100&Sheet1!B1:B100&Sheet1!C1:C100,0))
Posted by david m on January 29, 2002 6:23 AM
Thanks very much for the ideas - both seem to work and I appreciate it a lot!
