# Why does INDEX MATCH return the wrong value or #N/A?

Give MATCH a 0 for an exact match and start both ranges on the same row: =INDEX(C2:C8,MATCH(F1,A2:A8,0)).

**Excel:** `=INDEX(C2:C8,MATCH(F1,A2:A8,0))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Name | Team | Ext |  | Name | Dev |
| Lena | Finance | 2201 |  | Ext |  |
| Omar | Sales | 2207 |  |  |  |
| Noor | IT | 2215 |  |  |  |
| Tom | Sales | 2218 |  |  |  |
| Priya | Marketing | 2224 |  |  |  |
| Dev | IT | 2231 |  |  |  |
| Ines | Finance | 2236 |  |  |  |

Formula in F2: `=INDEX(C2:C8,MATCH(F1,A2:A8,0))`

Result: 2231

## 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.

## Other ways

- `=XLOOKUP(F1,A2:A8,C2:C8)`: Exact match by default, in one function.
- `=INDEX(C2:C8,XMATCH(F1,A2:A8))`: XMATCH matches exactly by default. Excel 2021 and later, and Google Sheets.
- `=INDEX(C1:C8,MATCH(F1,A2:A8,0))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/index-match-returns-wrong-value