VLOOKUP with two criteria?

By | January 12, 2018
Questions:

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.

Answers:

True = 1, False = 0

D1 returns 0 because 0 * 1 * 8 = 0

D2 returns 9 because 1 * 1 * 9= 9

enter image description here

This should let you change the criteria:
enter image description here

Questions:
Answers:

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”.

sample layout

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.

Questions:
Answers:

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:

Multiple Criteria INDEX MATCH

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)
Questions:
Answers:

Apparently you can use the SUMPRODUCT function.

Questions:
Answers:

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

Leave a Reply

Your email address will not be published. Required fields are marked *