INDEX MATCH Not Working? 7 Causes and Fixes

When INDEX MATCH is not working, you usually see #N/A or, worse, a wrong answer with no error. The cause is almost always one of seven small things: a missing 0, ranges that don't line up, a 2D range in MATCH, text versus numbers, hidden spaces, unlocked ranges, or INDEX pointing at the whole table. This guide shows how to spot and fix each one.

How INDEX MATCH works (and where it breaks)#

INDEX MATCH is two functions working together. MATCH finds the position of a value in a list. INDEX returns the value at that position from another list.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

All the examples use this staff list in A1:C7:

=INDEX(C2:C7,MATCH(1004,A2:A7,0)) returns IT. MATCH finds 1004 in position 4 of A2:A7, and INDEX returns the 4th value of C2:C7. To test a broken formula, check MATCH on its own first: type =MATCH(1004,A2:A7,0) in a spare cell. If that fails, the problem is in MATCH. If it works, the problem is in INDEX. The INDEX MATCH guide covers the basics.

Cause 1: MATCH is missing the 0#

MATCH's third argument is the match type, and if you leave it out, it defaults to 1, an approximate match. Approximate match assumes the list is sorted and returns the nearest smaller value when there's no exact match.

=INDEX(C2:C7,MATCH(1007,A2:A7))

There's no employee 1007, but this returns Sales, Farid's department, because 1006 is the largest ID below 1007. Add the 0 and you get #N/A, which correctly tells you the ID doesn't exist:

=INDEX(C2:C7,MATCH(1007,A2:A7,0))

On unsorted data, approximate match can return a value from a random-looking row. The MATCH function guide explains the three match types.

Cause 2: the ranges don't line up#

INDEX counts positions from the top of its own range, so both ranges must start on the same row. If they don't, every result is shifted.

=INDEX(C3:C8,MATCH(1004,A2:A7,0))

MATCH still returns 4, but the 4th cell of C3:C8 is C6, so the result is Finance instead of IT. No error, just the wrong row. If the INDEX range is shorter than the position MATCH returns, you get #REF! instead.

Check that both ranges cover exactly the same rows, such as A2:A7 and C2:C7.

Cause 3: MATCH looks in more than one column#

MATCH only works on a single row or a single column. If you give it a whole table, it returns #N/A:

=MATCH(1004,A2:C7,0)

Point MATCH at the one column that holds the lookup values, here A2:A7. For a two-way lookup (row and column), use two MATCH functions inside INDEX: one for the row and one for the column.

Cause 4: numbers stored as text#

The number 1004 and the text "1004" look identical, but MATCH treats them as different values. This happens when IDs are imported from another system, or when the lookup cell is formatted as Text. Text numbers usually sit on the left of the cell.

The best fix is to convert the data to real numbers: select the column, click the warning icon and choose Convert to Number. If you can't change the data, convert the lookup value inside the formula instead:

• Data is text, lookup value is a number: MATCH(E2&"",A2:A7,0) turns E2 into text.
• Data is numbers, lookup value is text: MATCH(VALUE(E2),A2:A7,0) turns E2 into a number.

Cause 5: hidden spaces#

A trailing space makes "Ben " different from "Ben", so MATCH can't find it. Test a cell with =LEN(B3): "Ben" should return 3.

Clean the data with TRIM in a helper column, then paste the results back as values. Or trim the lookup value in the formula: MATCH(TRIM(E2),B2:B7,0). That only fixes spaces in the lookup value, not in the data, so cleaning the column is the better long-term fix.

If TRIM doesn't help, the space may be a non-breaking space, a special character often copied from web pages. TRIM can't remove it. Use SUBSTITUTE to replace it first: =TRIM(SUBSTITUTE(B3,CHAR(160)," ")). CHAR(160) is the code for the non-breaking space. After cleaning, the LEN check should return the length you expect.

Cause 6: the ranges move when you copy the formula#

If you fill INDEX MATCH down a column, unlocked ranges slide down with it. In the next row, A2:A7 becomes A3:A8 and the first record is no longer searched, so some lookups return #N/A.

Lock both ranges with $ signs and leave the lookup cell unlocked:

=INDEX($C$2:$C$7,MATCH(E2,$A$2:$A$7,0))

Select each range in the formula and press F4 to add the $ signs (Mac: ⌘T).

Cause 7: INDEX gets the whole table without a column number#

If the INDEX range has several columns, INDEX needs a column number too. =INDEX(A2:C7,MATCH(1004,A2:A7,0)) has no column number. In Microsoft 365, it spills the whole row (1004, Dev, IT) across three cells, and shows #SPILL! if those cells aren't empty. Older versions may show #VALUE! or an unexpected value.

Either give INDEX a single column, as in all the examples above, or add the column number: =INDEX(A2:C7,MATCH(1004,A2:A7,0),3) returns IT. The INDEX function guide explains the row and column arguments.

If you have Microsoft 365 or Excel 2021, XLOOKUP avoids several of these traps, because it defaults to an exact match and needs no position numbers. See INDEX MATCH vs XLOOKUP for a side-by-side comparison, and XLOOKUP not working if you switch and hit new errors.

Practise it#

Practise INDEX MATCH in a live spreadsheet: you'll build lookups that go left and right, and the sheet checks each formula as soon as you press Enter. Then fix broken formulas in the lookup errors lesson, which covers #N/A, wrong rows and text numbers. Try the exercise on this page first.