Nested(?) Excel LOOKUP function

Question

I download a CVS file from a website that has a table I want to query.

The trick is that each row has TWO key fields and I need to match both to get the data I want.

Here is a simplified example:

Table has 5 columns, A. B. C. D. E. Keys are in columns B and D; data I want is in column C.

Table is from row 5 to row 100.

IF there is only ONE key, the formula is simple. Assuming the key I want to match is in cell A1:

=LOOKUP(A1,C5:C100,B5:B100)

What formula could I use if I want to ALSO match the key in column D

Answer 1

Try this formula to match what is entered in cells A1 and B1 as an array formula by pressing Ctrl+Shift+Enter instead of just Enter when done:

=INDEX($C$5:$C$100,MATCH(A1&B1,$B$5:$B$100&$D$5:$D$100,0))

Answer 2

Hi,

Mine is not an array formula.

Answer 3

Hi,

Try this

=INDEX(C$5:C$100,MATCH(1,INDEX((B$5:B$100=A1)*(D$5:D$100=B1),,),0),1)

Hope this helps.

Answer 4

Thanks. I will play around with it.

Answer 5

Ashish,

I used Jason's answer to good effect. (Thanks Jason -- I thought I posted a thanks but I don't see it here.)

I was able to understand the logic behind Jason's formula so feel comfortable using it. Does yours (Ashish) improve on that?  Is it an array formula?