Why does INDEX MATCH return the wrong value or #N/A?
Formula (Excel and Google Sheets)
=INDEX(C2:C8,MATCH(F1,A2:A8,0))Give MATCH a 0 for an exact match and start both ranges on the same row: =INDEX(C2:C8,MATCH(F1,A2:A8,0)).
- Result on the example
- 2231 in
F2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
MATCH's third argument defaults to 1, an approximate match that assumes the list is sorted. On these unsorted names it returns a wrong position or #N/A. With 0 it finds Dev at position 6, and INDEX returns the 6th value of C2:C8. Both ranges start on row 2, so position 6 is the same row in each.
=INDEX(C2:C8,MATCH(F1,A2:A8,0))Result2231
C2:C87 rows × 1 col: 2201, 2207, 2215, 2218, …A range: 7 cells in one column.
F1"Dev"The value in F1.
A2:A87 rows × 1 col: "Lena", "Omar", "Noor", "Tom", …A range: 7 cells in one column.
MATCH(F1,A2:A8,0)6MATCH: Position of a value in a list. 0 = exact match.- lookup_value
F1"Dev" - lookup_array
A2:A87 rows × 1 col: "Lena", "Omar", "Noor", "Tom", … - match_type
0
- lookup_value
INDEX(C2:C8,MATCH(F1,A2:A8,0))2231INDEX: Value at a given row and column of a range.- array
C2:C87 rows × 1 col: 2201, 2207, 2215, 2218, … - row_num
MATCH(F1,A2:A8,0)6
- array
In Google Sheets
The same formula works in Google Sheets: =INDEX(C2:C8,MATCH(F1,A2:A8,0)). We ran it in Google Sheets mode on the same data and it returns 2231.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =XLOOKUP(F1,A2:A8,C2:C8) | 2231 | Exact match by default, in one function. |
| =INDEX(C2:C8,XMATCH(F1,A2:A8)) | 2231 | XMATCH matches exactly by default. Excel 2021 and later, and Google Sheets. |
| =INDEX(C1:C8,MATCH(F1,A2:A8,0)) | 2224Different result | Shown for contrast: INDEX starts one row higher than MATCH. Returns the extension from the row above Dev's. |
Common mistakes
- Ranges that start on different rows, like
INDEX(C1:C7,MATCH(F1,A2:A8,0)), return the row above the right one, with no error. - MATCH returns a position inside its range, not a sheet row number.
- MATCH searches a single row or column. Given two columns, it returns #N/A.
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.
Why does INDEX MATCH return the wrong value or #N/A
F2, return the extension for the name in F1 with INDEX and MATCH.Questions
Why is my INDEX MATCH off by one row?
The INDEX and MATCH ranges start on different rows. Start both on the first data row.
How do I match on two criteria?
=INDEX(C2:C8,MATCH(1,(A2:A8=F1)*(B2:B8=F2),0)).
Why does INDEX MATCH return #REF!?
The position from MATCH is larger than the INDEX range, which happens when the INDEX range is shorter than the MATCH range.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.