Geek asks and answers

VLOOKUP with two criteria?

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

Thank.

+3
source share
5 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 will allow you to change the criteria: enter image description here

+5
source

For this I use INDEX / MATCH. Example:

I have a data table and you 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))

, Ctrl + Shift + Enter

Excel, , , .

+4

SUMPRODUCT , . , , - VLOOKUP, . :

Multiple Criteria INDEX MATCH

G2 : (Ctrl+Shift+Enter)

[edit: , ] =INDEX($C$1:$C$6,MATCH(E2&"|"&F2,$A$1:$A$6&"|"&$B$1:$B$6,0))

:

  • SUMPRODUCT , .
  • SUMPRODUCT SUM , , ( VLOOKUP)
+3

Obviously, you can use the SUMPRODUCT function .

0
source

In fact, I think that it sets the typical display of results in Excel. This can be done using the Small and row functions in arrays .

Display all results matching various criteria

0
source

More articles:


All Articles