The formula
ExcelExcel
=INDEX(C2:C7,MATCH(1,(A2:A7=B9)*(B2:B7=B10),0))In E2 → 75
No Ctrl+Shift+Enter needed (any Excel)Excel
=INDEX(C2:C7,MATCH(1,INDEX((A2:A7=B9)*(B2:B7=B10),0),0))In E3 → 75
Google SheetsGoogle Sheets
=ARRAYFORMULA(INDEX(C2:C7,MATCH(1,(A2:A7=B9)*(B2:B7=B10),0)))In E4 → 75
✓ 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 | 75 | |
| 3 | West | Pens | 95 | 75 | |
| 4 | East | Paper | 180 | 75 | |
| 5 | North | Pens | 60 | ||
| 6 | West | Paper | 210 | ||
| 7 | East | Ink | 75 | ||
| 8 | |||||
| 9 | Region | East | |||
| 10 | Product | Ink |
How it works
- C2:C7
- The column you want a value from.
- MATCH(1, … ,0)
- Finds the position of the first row where the conditions array equals 1. The final 0 means exact match.
- (A2:A7=B9)*(B2:B7=B10)
- 1 where both conditions are TRUE, 0 elsewhere. Chain more with *(range=value).
- INDEX(…,0)
- The inner INDEX(array,0) trick makes older Excel evaluate the array without Ctrl+Shift+Enter.
Common errors and fixes
Why does the formula return #N/A?
No row meets all conditions, or numbers stored as text don't equal real numbers. Check with =ISNUMBER(cell).
Why does the formula return #VALUE! in Excel 2019 or older?
The formula was entered normally. Press Ctrl+Shift+Enter, or use the INDEX(…,0) version.
Returns the wrong row?
Ranges are offset (A2:A7 vs C1:C6). Start all ranges on the same row.
Does this work in Google Sheets?
Yes. Use the formulas tagged "Google Sheets" on this page; formulas tagged only "Excel" use Excel-specific syntax.