How to look up a value with two criteria in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(1,(A2:A8=G1)*(B2:B8=G2),D2:D8)Multiply two tests to build the lookup array: =XLOOKUP(1,(range1=crit1)*(range2=crit2),result_range).
- Result on the example
- $55,300 in
G3 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.
How it works
Each comparison returns TRUE or FALSE for every row. Multiplying them turns TRUE×TRUE into 1 and anything else into 0, so only the row that matches both criteria is a 1. XLOOKUP then finds the 1 and returns the sales from that row.
=XLOOKUP(1,(A2:A8=G1)*(B2:B8=G2),D2:D8)Result55300
A2:A87 rows × 1 col: "East", "West", "East", "West", …A range: 7 cells in one column.
G1"East"The value in G1.
A2:A8=G1{7 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
B2:B87 rows × 1 col: "Q1", "Q1", "Q2", "Q2", …A range: 7 cells in one column.
G2"Q2"The value in G2.
B2:B8=G2{7 rows × 1 col} FALSE, FALSE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
A2:A8=G1)*(B2:B8=G2{7 rows × 1 col} 0, 0, 1, 0, …The * operator multiplies.
D2:D87 rows × 1 col: $48,200, $51,900, $55,300, $49,800, …A range: 7 cells in one column.
XLOOKUP(1,(A2:A8=G1)*(B2:B8=G2),D2:D8)55300XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
1 - lookup_array
A2:A8=G1)*(B2:B8=G2{7 rows × 1 col} 0, 0, 1, 0, … - return_array
D2:D87 rows × 1 col: $48,200, $51,900, $55,300, $49,800, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(1,(A2:A8=G1)*(B2:B8=G2),D2:D8). We ran it in Google Sheets mode on the same data and it returns $55,300.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(D2:D8,MATCH(1,(A2:A8=G1)*(B2:B8=G2),0)) | $55,300 | The same idea with INDEX and MATCH, for Excel 2019 and earlier (confirm with Ctrl+Shift+Enter in Excel 2016 and older). |
| =SUMIFS(D2:D8,A2:A8,G1,B2:B8,G2) | $55,300 | When the result is a number and each pair appears once, SUMIFS is simplest. It returns 0 instead of #N/A when there is no match. |
Common mistakes
- If two rows match both criteria, XLOOKUP returns the first. Use FILTER to see them all.
- A helper column joining the keys (
=A2&"|"&B2) is another option, but it has to be kept in sync with the data.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Look up a value with two criteria
G3, return the sales for the region in G1 and the quarter in G2.Questions
Can VLOOKUP use two criteria?
Only with a helper column that joins the two keys. XLOOKUP or INDEX/MATCH with multiplied tests is cleaner.
How do I use three criteria?
Multiply a third test: (A2:A8=G1)*(B2:B8=G2)*(C2:C8=G4).
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.