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

SUMPRODUCT definitely has value when the sum over multiple criteria matches is needed. But the way I read your question, you want something like VLOOKUP that returns the first match. Try this:

For your convenience the formula in G2 is as follows -- requires array entry (Ctrl+Shift+Enter)

[edit: I updated the formula here but not in the screen shot] =INDEX($C$1:$C$6,MATCH(E2&"|"&F2,$A$1:$A$6&"|"&$B$1:$B$6,0))

Two things to note:

1. SUMPRODUCT won't work if the result type is not numeric
2. SUMPRODUCT will return the SUM of results matching the criteria, not the first match (as VLOOKUP does)

Answer 2

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 3

Apparently you can use the SUMPRODUCT function.

Answer 4

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.

Answer 5

Actually, I think what he is asking is typical multiple results display option in excel. It can be done using Small, and row function in arrays.

This display all the results that matches the different criteria