Vlookup based on two lookup values
 

Dear All,

How would I write a vlookup type statement that checks against two values,
rather than one. For example I want the formula to check a list for rows that
have the same values as Column A and B, then take the value from column 3 in
the vlookup range. Although the formula below does not work it might make it
a little clearer.

=VLOOKUP(A2 AND B2,OtherSheet!A1:G10,4,FALSE)

Thanks,
Trip

I like this syntax:

=index(othersheet!d1:d10,
match(1,(a2=othersheet!a1:a10)*(b2=othersheet!b1:b 10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)



Trip wrote:

Dear All,

How would I write a vlookup type statement that checks against two values,
rather than one. For example I want the formula to check a list for rows that
have the same values as Column A and B, then take the value from column 3 in
the vlookup range. Although the formula below does not work it might make it
a little clearer.

=VLOOKUP(A2 AND B2,OtherSheet!A1:G10,4,FALSE)

Thanks,
Trip

--

Dave Peterson

I suggest handling the problem through concatenation.
Create a new column as first column in the table. A1:G10 will become B1:H10.
Use formula A1=B1&"|"&C1
Now the following formula should work
=VLOOKUP(A2&"|"&B2,OtherSheet!A1:H10,5,FALSE)

T G Sekhar