The formula
ExcelExcel
=XLOOKUP(1,(A2:A7=B9)*(B2:B7=B10),C2:C7,"Not found")In E2 → 210
Excel 2010-2019 (INDEX/MATCH)Excel
=INDEX(C2:C7,MATCH(1,(A2:A7=B9)*(B2:B7=B10),0))In E3 → 210
Google SheetsGoogle Sheets
=INDEX(FILTER(C2:C7,A2:A7=B9,B2:B7=B10),1)In E4 → 210
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 1 result for formulas tagged Google Sheets was checked with a reference implementation of the documented behavior, because LibreOffice can't run that function.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | ||
| 2 | East | Pens | 120 | 210 | |
| 3 | West | Pens | 95 | 210 | |
| 4 | East | Paper | 180 | 210 | |
| 5 | North | Pens | 60 | ||
| 6 | West | Paper | 210 | ||
| 7 | East | Ink | 75 | ||
| 8 | |||||
| 9 | Region | West | |||
| 10 | Product | Paper |
How it works
- 1
- The value to find. Each row of the test array is 1 only where every condition is TRUE.
- (A2:A7=B9)*(B2:B7=B10)
- Two TRUE/FALSE arrays multiplied together. TRUE×TRUE = 1, anything with a FALSE = 0. Add more conditions with another *(range=value).
- C2:C7
- The column to return a value from. Must be the same height as the condition ranges.
- "Not found"
- Optional if_not_found text, shown instead of #N/A when no row matches all conditions.
Common errors and fixes
Why does the formula return #N/A?
No row matches every condition, or a value has a stray space. Add the if_not_found argument, and TRIM your data.
Why does the formula return #VALUE!?
The condition ranges are different sizes. Make every range cover the same rows, e.g. all A2:A7, B2:B7, C2:C7.
Wrong result?
Duplicate matches: XLOOKUP returns the first one from the top. Use FILTER to return all matches.
Does this work in Google Sheets?
Yes. Use the formulas tagged "Google Sheets" on this page; formulas tagged only "Excel" use Excel-specific syntax.