Fix the #N/A error

Excel & Google Sheets

What #N/A means

INDEX MATCH returns #N/A when the MATCH part cannot find the lookup value in the match_array. MATCH returns #N/A, and INDEX then propagates that error to the cell. The lookup value either does not exist, does not match due to a type or spacing difference, or the arguments point to the wrong range.

Common causes

The match_type argument in MATCH is 0 (exact) but there is a hidden trailing space or a type mismatch preventing recognition of an existing value.
The match_type is 1 or -1 (approximate) and the match_array is not sorted in the required order, so MATCH stops at the wrong position.
The match_array and the index_array are different lengths — MATCH returns a position number and INDEX uses it to look up a different array that ends earlier.
The lookup value is a number but the match_array contains text representations of numbers, or the reverse — they look the same but compare as unequal.
The wrong column or row was selected for the match_array — a common copy-paste error when reusing a formula.

Example fix

Broken

=INDEX($E$2:$E$100,MATCH(A2,$D$2:$D$100))

Fixed

=INDEX($E$2:$E$100,MATCH(TRIM(A2),$D$2:$D$100,0))

Added the required match_type 0 (exact match) which was omitted, causing approximate-match behaviour on unsorted data. Also added TRIM to handle hidden spaces.

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Cell row 1 column 1	Cell row 1 column 2
Cell row 2 column 1	Cell row 2 column 2
Cell row 3 column 1	Cell row 3 column 2
Cell row 4 column 1	Cell row 4 column 2

Formula

Runs server-side · free · no signup

How to fix it

1Always use 0 as the third argument to MATCH for exact lookups: =MATCH(A2,$D$2:$D$100,0). Omitting it defaults to 1 (approximate match with sorted assumption).
2Check data types: use =ISNUMBER(D2) or =ISTEXT(D2) in a helper column to audit the match_array. Convert text-numbers with VALUE() or multiply by 1.
3Strip extra spaces: =MATCH(TRIM(A2),D2:D100,0) and apply TRIM to the match_array if the source is pasted data.
4Verify both arrays are the same length: the match_array in MATCH and the array in INDEX must cover exactly the same rows. Anchor both with absolute references to the same row range.
5Test MATCH alone first: =MATCH(A2,$D$2:$D$100,0) in an empty cell. If it returns #N/A, the problem is in the lookup; if it returns a number, the problem is in the INDEX part.

Stop hunting errors by hand.

Upload your spreadsheet and the Auditor flags every #N/A and broken formula at once — or paste this one formula and get the fix explained.

Frequently asked

Why use INDEX MATCH instead of VLOOKUP?

INDEX MATCH can look in any direction (left, right, up, down), does not break when columns are inserted into the source table, and is generally faster on large datasets.

My MATCH formula returns a number but INDEX still returns #N/A — why?

The position MATCH returns is outside the bounds of the array given to INDEX. Make sure both arrays start and end on the same rows. For example, if MATCH searches D2:D100, INDEX must use a range that also starts at row 2.

Can INDEX MATCH return multiple columns at once?

In Excel 365 and Google Sheets you can pass an array of column numbers: =INDEX($E$2:$G$100,MATCH(A2,$D$2:$D$100,0),{1,2,3}). In older Excel, you need separate INDEX MATCH formulas for each column.

Related formulas

INDEX MATCH INDEX / MATCH XLOOKUP VLOOKUP IFERROR

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: May 28, 2026