📎 This article includes 1 downloadable practice file ↓
Most real lookups need more than one key: the price of a product in a region, the sales of a rep in a month. Here are three reliable ways, from newest to most compatible.
In this article
1. XLOOKUP with a Boolean trick (Microsoft 365 / 2021)
=XLOOKUP(1, (A2:A100 = G2) * (B2:B100 = H2), D2:D100, "Not found")How it works: (A2:A100=G2) produces TRUE/FALSE for every row. Multiplying two of those arrays turns them into 1s and 0s — a row is 1 only when both conditions are true. XLOOKUP then looks for the first 1.
(A=G2)*(B=H2)*(C>=I2). Use + inside brackets for OR: ((A=G2)+(A=G3))*(B=H2).2. INDEX + MATCH (every Excel version)
=INDEX(D2:D100, MATCH(1, (A2:A100 = G2) * (B2:B100 = H2), 0))Same idea: MATCH finds the row where the product of the conditions is 1. In Excel 2019 and earlier you must confirm it with Ctrl+Shift+Enter because it is an array formula.
3. Helper column (fastest on huge sheets)
E2: =A2 & "|" & B2
Lookup: =XLOOKUP(G2 & "|" & H2, E2:E100, D2:D100, "Not found")Joining the keys into one text value lets any normal lookup work, and it is the quickest option on 100,000+ rows because the combined key is calculated once. The | separator prevents “AB”+“C” colliding with “A”+“BC”.
Which should you use?
| Situation | Use |
|---|---|
| Microsoft 365, normal size data | XLOOKUP with Boolean logic |
| File shared with older Excel | INDEX-MATCH (Ctrl+Shift+Enter) |
| Very large data or many lookups | Helper column |
| You need a SUM, not a single value | SUMIFS — no lookup needed |
Common mistakes
- Numbers stored as text in one of the key columns: the condition is never true. Check with
=ISNUMBER(A2). - Ranges of different sizes (A2:A100 vs B2:B99) give
#VALUE!. - Several rows match: all methods return the first. Use XLOOKUP’s
search_mode -1for the last one, or return all matches.
See lookups animated in the Formula Lab.
Try it yourself: step by step
- Download two-criteria-lookup.xlsx. The Prices sheet has every product in every region (24 rows) with a slightly different price.
- On the Lookup sheet set
B1= Monitor andB2= East. All four methods return the same price. - Change B2 to
Central(a region that does not exist): the XLOOKUP versions say “Not found”, INDEX-MATCH shows#N/Aand SUMIFS returns 0 — a silent wrong answer to watch out for. - Look at column C on Prices: the helper key
=A2&"|"&B2turns two conditions into one text value. - Duplicate a row on Prices with a different price. XLOOKUP and INDEX-MATCH return the first match; SUMIFS adds both. Use SUMIFS only when each combination is unique.
📎 Practice files for this article
- 📗Two-criteria lookup workbook24 product-region prices with XLOOKUP, INDEX-MATCH, helper-column and SUMIFS versions.⬇ XLSX · 7 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.