VLOOKUP with two criteria?

Question

Is there a formula that returns a value from the first line matching two or more criteria? For example, "return column C from the first line where column A = x AND column B = y". I'd like to do it without concatenating column A and column B.

Thanks.

Answer 1

True = 1, False = 0

D1 returns 0 because 0 * 1 * 8 = 0

D2 returns 9 because 1 * 1 * 9= 9

This should let you change the criteria:

Answer 2

I use INDEX/MATCH for this. Ex:

I have a table of data and want to return the value in column C where the value in column A is "c" and the value in column B is "h".

I would use the following array formula:

=INDEX($C$1:$C$5,MATCH(1,(($A$1:$A$5="c")*($B$1:$B$5="h")),0))

Commit the formula by pressing Ctrl+Shift+Enter

After entering the formula, you can use Excel's formula auditing tools to step through the evaluation to see how it calculates.