=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7) returns the price from the row where the product is E2 and the size is F2. Each comparison checks every row, multiplying them gives 1 only where both are true, and XLOOKUP looks for that 1. It needs Excel 2021 or Microsoft 365; the INDEX MATCH version below works in every version.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Tea | Large | $3.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
Tea and Large meet in row 5, so G2 returns 3.00 again, from another row. Add a fourth argument for the case where no row matches both: =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7,"No such item").
How the multiplied conditions work
A2:A7=E2 compares every product with E2 and returns six TRUE or FALSE values. Multiplying two such lists turns TRUE into 1 and FALSE into 0, and a row is 1 only if it is 1 in both. Column D shows that list, spilled from one formula.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Both match | Product | Size | |
| 2 | Coffee | Small | $2.50 | 0 | Tea | Large | |
| 3 | Coffee | Large | $3.50 | 0 | |||
| 4 | Tea | Small | $2.00 | 0 | |||
| 5 | Tea | Large | $3.00 | 1 | |||
| 6 | Juice | Small | $3.00 | 0 | |||
| 7 | Juice | Large | $4.00 | 0 |
Only D5 is 1. Change F2 or G2 and the 1 moves. Every extra criterion is one more *(range=value), and the conditions do not have to be equality: *(C2:C7<3) adds "price below 3". Every range must cover the same rows (A2:A7, B2:B7, C2:C7): if the return range has a different size from the conditions, XLOOKUP returns #VALUE!.
INDEX MATCH with multiple criteria
For Excel 2019 and older, MATCH can search the same array for the 1 and INDEX returns the price from that position.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Coffee | Large | $3.50 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
Coffee and Large is position 2 of the array, and INDEX returns $3.50. In Excel 2019 and older this is an array formula: press Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac) instead of Enter, and Excel shows it inside curly braces. Pressing plain Enter there usually returns #N/A or #VALUE!. In Excel 365, Enter is enough. The single-criterion form is on the INDEX and MATCH page.
Join the criteria into one key
The other way is to turn two criteria into one by joining them. VLOOKUP needs the joined values in a helper column at the start of the table (the VLOOKUP page shows that version). XLOOKUP can join the ranges inside the formula, so no helper column is needed.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Juice | Large | $4.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
A2:A7&"|"&B2:B7 builds six keys such as Juice|Large, and XLOOKUP finds Juice|Large among them: $4.00. Put a separator between the parts. Without it, "AB" and "C" join to the same "ABC" as "A" and "BC", and the lookup can return the wrong row.
If the value you want is a number and each combination appears once, SUMIFS gives the same answer with no array at all: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2). It returns 0 instead of an error when nothing matches, which can hide a typo.
Return every match with FILTER
XLOOKUP and INDEX MATCH return the first matching row. When several rows match and you want all of them, use FILTER with the same conditions.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Matches | ||
| 2 | North | Laptop | Q1 | 120 | Q1 | 210 | |
| 3 | North | Phone | Q1 | 210 | Q2 | 225 | |
| 4 | South | Laptop | Q1 | 95 | Q3 | 240 | |
| 5 | North | Phone | Q2 | 225 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | North | Laptop | Q2 | 135 | |||
| 8 | North | Phone | Q3 | 240 |
Three rows are North and Phone, so F2 spills their quarters and sales into F2:G4. Change A3 to South and the list shrinks to two. If no row matches, FILTER returns #CALC!; a third argument such as "None" shows text instead. More options are on the FILTER page.
Practice: three criteria
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Region | South | |
| 2 | North | Laptop | Q1 | 120 | Product | Laptop | |
| 3 | North | Laptop | Q2 | 135 | Quarter | Q2 | |
| 4 | North | Phone | Q1 | 210 | Sales | ||
| 5 | South | Laptop | Q1 | 95 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | South | Laptop | Q2 | 110 | |||
| 8 | North | Phone | Q2 | 225 |
Your turn: Return in G4 the sales for the region in G1, the product in G2 and the quarter in G3.
Frequently Asked Questions
How do I use XLOOKUP with multiple criteria?
Multiply one comparison per criterion and look up 1: =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7). Each comparison gives TRUE or FALSE per row, the product is 1 only where all are TRUE, and XLOOKUP returns the first such row.
How do I do INDEX MATCH with two criteria?
Use the same multiplied conditions inside MATCH: =INDEX(C2:C7,MATCH(1,(A2:A7=E2)*(B2:B7=F2),0)). In Excel 2019 and older, confirm it with Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac).
Can VLOOKUP use two criteria?
Not directly. Add a helper column at the start of the table that joins the two values, such as =A2&"|"&B2, then look up the joined value: =VLOOKUP(E2&"|"&F2,helper_table,col,FALSE).
Can SUMIFS replace a lookup with two criteria?
Yes, when the value is a number and each combination appears once: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2). It returns 0 instead of #N/A when no row matches, and adds the values together if a combination appears twice.
How do I look up with OR criteria?
Add the conditions instead of multiplying them: (A2:A7="Tea")+(A2:A7="Juice") is 1 or more where either is true. Look up a value greater than 0, for example with =XLOOKUP(TRUE,((A2:A7="Tea")+(A2:A7="Juice"))>0,C2:C7).
