Sometimes one column is not enough to find the right row. For example, you need the price for a specific product in a specific region.
Method 1: multiply the conditions
=XLOOKUP(1, (A2:A100="North")*(B2:B100="Laptop"), C2:C100, "Not found")
Each test returns TRUE or FALSE. Multiplying them gives 1 only where both are true, so XLOOKUP searches for 1.
Using cells for the criteria
=XLOOKUP(1, (A2:A100=F1)*(B2:B100=F2), C2:C100, "Not found")
Method 2: join the columns
Search for a combined text:
=XLOOKUP(F1&"|"&F2, A2:A100&"|"&B2:B100, C2:C100)
The | separator avoids accidental matches.
Method 3: FILTER
=FILTER(C2:C100, (A2:A100=F1)*(B2:B100=F2), "None") returns every match, not just the first.
For older Excel
Use INDEX and MATCH with the multiplication trick inside MATCH, entered as an array formula in versions before 365.
All ranges must have the same number of rows.
Worked example
Find the sales for Region North and Product Pen: =XLOOKUP(1,(A2:A100="North")*(B2:B100="Pen"),C2:C100,"Not found"). The two tests multiply to 1 only for the matching row.
Common mistakes
- Using AND(), which returns one value.
- Ranges of different length.
- Expecting more than one result. XLOOKUP gives the first.
Frequently asked questions
Can I join columns instead?
Yes: =XLOOKUP(F2&G2,A2:A100&B2:B100,C2:C100).
How do I get all matches?
Use FILTER.
Does it need Ctrl+Shift+Enter?
No.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: XLOOKUP, FILTER, INDEX.
Start Excel practice → Case studies
