|
|
| Lookup
a Value based on Multiple Criteria |
Without a Helper Column
The
following formulas return a value from D2:D10, where the corresponding
value in B2:B10 equals the value in F2, and the corresponding value in
C2:C10 equals the value in G2...
=INDEX(D2:D10,MATCH(1,IF(B2:B10=F2,IF(C2:C10=G2,1)),0))
or
=INDEX(D2:D10,MATCH(F2&"#"&G2,B2:B10&"#"&C2:C10,0))
| Note
that these are array formulas, which need
to
be confirmed with
CONTROL+SHIFT+ENTER for the PC or COMMAND+RETURN for the Mac. |
Based on the sample data, the formulas return 20. Sample
Workbook
With a
Helper Column
First, the following formula is entered in E2 and copied down so that
each
value in B2:B10 is concatenated with its corresponding value in
C2:C10...
=B2&"#"&C2
Then,
the following formula returns a value from D2:D10, where the
corresponding value in E2:E10 equals the concatenated value of G2
and H2...
=INDEX(D2:D10,MATCH(G2&"#"&H2,E2:E10,0))
Based on the sample data, the formula returns 20. Sample
Workbook
|
|
|
