XLOOKUP with Multiple Criteria (Product and City)
Zoya's price list has Milk 1L three times: Pune ₹56, Nashik ₹54 and Nagpur ₹55. A normal =XLOOKUP("Milk 1L",A2:A9,C2:C9) always returns the first one, ₹56. She needs the price for Milk 1L in Nashik, so the lookup must match two columns at once.
XLOOKUP can match several conditions if you multiply them: (A2:A9=G2)*(B2:B9=H2) gives 1 only on the row where both are true. XLOOKUP then looks for that 1.
Download the practice file (xlookup-multiple-criteria.xlsx)
Excel file, 6 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.
Quick answer
- Put the product in G2 and the city in H2.
- In I2 type
=XLOOKUP(1,(A2:A9=G2)*(B2:B9=H2),C2:C9,"Not found"). - Press Enter (Microsoft 365 or Excel 2021 and later).
- Change G2 or H2 and the price updates.
- For Excel 2019 or older, use the helper-column method below.
=XLOOKUP(1,(A2:A9=G2)*(B2:B9=H2),C2:C9,"Not found")
The price list
| Product | City | Price |
|---|---|---|
| Milk 1L | Pune | 56 |
| Milk 1L | Nashik | 54 |
| Milk 1L | Nagpur | 55 |
| Bread | Pune | 40 |
| Bread | Nashik | 38 |
| Paneer 200g | Pune | 90 |
| Paneer 200g | Kolhapur | 85 |
| Curd 400g | Solapur | 35 |
How the formula works
A2:A9=G2checks every product and gives TRUE or FALSE for each row.B2:B9=H2does the same for the city.- Multiplying them turns TRUE × TRUE into 1 and anything with FALSE into 0.
- Only the Milk 1L + Nashik row becomes 1.
XLOOKUP(1, …)finds that 1 and returns the price from C2:C9: 54.
Try Paneer 200g + Kolhapur: the result is 85. Bread + Nagpur is not in the list, so the result is Not found.
Method 2: helper column (works in every version)
- Insert a column D with the header Key.
- In D2 type
=A2&"|"&B2and copy it down. D3 showsMilk 1L|Nashik. - Look up the joined key:
=XLOOKUP(G2&"|"&H2,D2:D9,C2:C9,"Not found"). - In Excel 2019 or older use
=IFERROR(INDEX(C2:C9,MATCH(G2&"|"&H2,D2:D9,0)),"Not found").
The | sign stops false matches such as "Pune"&"Milk" looking the same as "PuneM"&"ilk".
Method 3: SUMIFS for numbers
If the answer is a number and each combination appears only once, SUMIFS also works: =SUMIFS(C2:C9,A2:A9,G2,B2:B9,H2) gives 54. If the combination is missing it returns 0 (not an error), and if it appears twice it adds both, so use it only on clean lists.
Ravindra Bagale's Tip
Always add the fourth argument of XLOOKUP, like "Not found". A clear message is better than #N/A when a city is missing from the price list.
Ravindra Bagale's Tip – मराठी
XLOOKUP चा चौथा argument नेहमी द्या, उदा. "Not found". Price list मध्ये city नसली तर #N/A पेक्षा स्पष्ट message जास्त बरा.
Ravindra Bagale's Tip – हिंदी
XLOOKUP का चौथा argument हमेशा दीजिए, जैसे "Not found"। Price list में city न हो तो #N/A से साफ़ message बेहतर है।
Practice task
- Open the Prices sheet. G2 has Milk 1L and H2 has Nashik.
- Check that I2 shows 54.
- Change H2 to Pune. Expect 56.
- Change G2 to Bread and H2 to Nagpur. Expect Not found.
- Check the helper-column answer in I5 and the SUMIFS answer in I6.
Learn it properly
Course lessons:
- 4.6 XLOOKUP Basics and if_not_found
- 4.7 XLOOKUP Match Modes, Search Modes and Multiple Returns
- 4.4 INDEX and MATCH
Related guides: XLOOKUP for Beginners · INDEX and MATCH · SUMIF and SUMIFS · IFERROR vs IFNA in Excel: Replace #N/A and #DIV/0! the Right Way
Frequently asked questions
Why does XLOOKUP return the first match only?
XLOOKUP stops at the first row that matches. With duplicate products you must add more conditions, such as the city.
Do I need Ctrl + Shift + Enter?
Not in Microsoft 365 or Excel 2021 and later. Older versions do not have XLOOKUP; use the helper column with INDEX and MATCH.
Can I use three conditions?
Yes. Multiply one more test: (A2:A9=G2)*(B2:B9=H2)*(D2:D9=J2).
Which method is fastest on big files?
The helper column. It is calculated once per row and the lookup is a simple exact match.