VLOOKUP with two variables

[Paul]

I am struggling with a VLOOKUP with two variables.

Cell I3 =IF(lookup(D3,Sheet2!A1:C8,3,FALSE)=I2,lookup(D3,Sheet2!A1:C8,3,FALSE),
What???)

I want the lookup to find Sheet1 col D in Sheet2, where Sheet2 ColA
matches, and then the adjacent value in col B matches Sheet1!I2 then
returns Sheet2!Col C value.

then similarly with Sheet1!Col L

Sheet1
1 D E F G H I J K L
2 ch W4 NM J5 HR 11
3 901032 0.25 0.33 Y #NA #NA
4 901033 342.20 456.27 N 2 1
5 901034 58.60 78.13 N 2 3
6 901036 97.00 129.33 N 1 3
7 901037 142.00 189.33 N #NA 3

Sheet2
A B C
1 901033 11 1
2 901033 NM 2
3 901034 11 3
4 901034 NM 2
5 901035 NM 4
6 901036 11 3
7 901036 NM 1
8 901037 11 3

Appreciate any help on this one.

 

[Jerry W. Lewis]

Not very elegant, but you could do

=IF(MIN(IF((D3=Sheet2!A1:A8)*(I3=Sheet2!B1:B8),ROW(Sheet2!A1:A8)))>0,
INDEX(Sheet2!C1:C8,MIN(IF((D3=Sheet2!A1:A8)*(I3=Sheet2!B1:B8),ROW(Sheet2!A1:A8)))),
"")

array entered (Ctrl+Shift+Enter). The IF() wrapper is required because
INDEX() returns the entire row or column if the row/column reference is
zero.

Jerry

 

[Paul]

thanks for the help Jerry.
but I have since found another way, by concatenating my two variables
into another column and performing a lookup on that.

Thanks again.
Cheers Paul