Lookup
Multi-Criteria Lookup
Perform a lookup that matches on multiple criteria simultaneously. Essential when a single column isn't unique enough to identify the right row.
Excel Formula
=INDEX(D2:D100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))Step-by-Step Explanation
1
This is an array formula (press Ctrl+Shift+Enter in Excel)
2
(A2:A100=F2) checks first criteria, returning TRUE/FALSE
3
(B2:B100=G2) checks second criteria
4
Multiplying the conditions creates an AND logic
5
MATCH finds the row where both conditions are TRUE
6
INDEX returns the value from that row
Example
| Region (A) | Product (B) | Month (C) | Sales (D) |
|---|---|---|---|
| West | Widget | Jan | $5,000 |
| East | Widget | Jan | $3,200 |
| West | Gadget | Jan | $4,100 |
Result: =INDEX(D2:D100,MATCH(1,(A2:A100="West")*(B2:B100="Widget"),0)) → $5,000
Common Variations
XLOOKUP with concat
=XLOOKUP(F2&G2,A2:A100&B2:B100,D2:D100)Excel 365: cleaner syntax
Three criteria
=INDEX(D:D,MATCH(1,(A:A=F2)*(B:B=G2)*(C:C=H2),0))Add more conditions by multiplying
SUMPRODUCT approach
=SUMPRODUCT((A2:A100=F2)*(B2:B100=G2)*D2:D100)Sum matching values instead of lookup
Need a Custom Version?
Use our AI generator to create a formula tailored to your specific data and requirements.
Try It with AI →