MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!