XLOOKUP With Two Criteria in Excel (Multiple Conditions, Joined Key)
XLOOKUP finds one value, but sometimes you need two conditions, like the sales for a product in a month. Build a joined key with the ampersand and look it up, then see the no-helper-column version that multiplies two matches.
Try it yourself
Copy these tables into a blank sheet, then follow the steps below.
Paste at cell A1:
| Product | Month | Sales |
|---|---|---|
| Laptop | Jan | 1,200 |
| Laptop | Feb | 1,350 |
| Laptop | Mar | 1,100 |
| Phone | Jan | 800 |
| Phone | Feb | 950 |
| Phone | Mar | 700 |
| Tablet | Jan | 400 |
| Tablet | Feb | 450 |
Paste at cell F1:
| Product | Month | Sales | No helper |
|---|---|---|---|
| Phone | Feb | ||
| Tablet | Jan | ||
| Laptop | Mar |
Step by step
- XLOOKUP, 2 criteria. XLOOKUP finds a value using 1 condition. When you need 2, like the sales for a product in a month, you can join them into a single key.
- The sales list. The sales list in A1:C9 has Product, Month and Sales. Phone appears 3 times, once for each month, so the product name alone can't find the right row.
- What we want. The table in F1:H4 holds 3 product and month pairs. Column H will show the sales for each pair.
- Make a key. Add a helper column next to the list: type Key in D1. In D2, type =A2&B2. The ampersand joins the product and the month into one piece of text, so Phone and Feb become PhoneFeb. Fill D2 down to D9. Now every row has a key that appears only once in the list.
D1
KeyD2
=A2&B2 - Look up the key. In H2, type =XLOOKUP(F2&G2,$D$2:$D$9,$C$2:$C$9). What to find is F2 joined to G2, where to look is the key column, and what to give back is the sales column. Fill H2 down to H4. H2 returns 950 for Phone in Feb, and the other 2 pairs fill in the same way. Format H2:H4 with the number format #,##0, so the answers use a thousands comma like the sales column.
H2
=XLOOKUP(F2&G2,$D$2:$D$9,$C$2:$C$9) - No helper column. To skip the helper column, put this in I2: =XLOOKUP(1,($A$2:$A$9=F2)*($B$2:$B$9=G2),$C$2:$C$9). The part ($A$2:$A$9=F2)*($B$2:$B$9=G2) gives 1 only on the row where both match. XLOOKUP looks for that 1 and returns the sales. Fill I2 down to I4. You get the same answers with no extra column. It's harder to read, though, so the key column is the better choice when other people will edit the sheet.
I2
=XLOOKUP(1,($A$2:$A$9=F2)*($B$2:$B$9=G2),$C$2:$C$9) - Two ways. For 2 criteria, either join them into 1 key with the ampersand and look that key up, or multiply 2 matches together and look for 1.
Common mistakes
- Joining the parts in a different order. The key is built as product then month (=A2&B2), so the lookup value must be built in the same order, F2&G2. G2&F2 gives FebPhone, which isn't in the list, so XLOOKUP returns #N/A.
- Leaving out the $ signs. Without $D$2:$D$9 and $C$2:$C$9, filling H2 down moves the ranges down 1 row each time, so lower rows can miss data or return #N/A. Lock the ranges and leave F2 and G2 unlocked.
- Keys that run together. Different pairs can join into the same text, like "AB"&"C" and "A"&"BC", which both give ABC. Put a separator in both places, such as =A2&"|"&B2 and F2&"|"&G2.
- Months stored as real dates. If the month column holds real dates formatted to show Feb, the & joins the date's serial number, not the word Feb. Build the key with TEXT(B2,"mmm") instead, and type the lookup month the same way.
When a business analyst uses this
In a requirements log where each requirement ID appears once per release, the ID alone isn't unique. Join the ID and the release into a key, and XLOOKUP can pull the status of REQ-014 in Release 2 into the sign-off summary without picking up the wrong release's row.
Questions people ask
Can XLOOKUP use multiple criteria?
Yes. Join the criteria into 1 key with &, like F2&G2, and look it up in a helper column built the same way. You can also multiply matches, like =XLOOKUP(1,($A$2:$A$9=F2)*($B$2:$B$9=G2),$C$2:$C$9).
How do I do an XLOOKUP with 2 criteria without a helper column?
Look up the number 1 in the product of 2 tests: =XLOOKUP(1,($A$2:$A$9=F2)*($B$2:$B$9=G2),$C$2:$C$9). Each test gives 1 or 0, so only the row where both are true gives 1.
How do I look up with 2 criteria in older Excel without XLOOKUP?
XLOOKUP needs Microsoft 365, Excel 2021 or later. In older versions, keep the same key column and use =INDEX($C$2:$C$9,MATCH(F2&G2,$D$2:$D$9,0)). VLOOKUP can't do it here, because the key in column D sits to the right of the sales in column C.
