INDEX MATCH not working: causes and fixes

Quick diagnosis: test MATCH on its own

The sheet is an office-supplies stock list with 9 items. The IDs were partly typed and partly pasted from a supplier file, so one ID is stored as text and one product name has a trailing space. That is how most broken lookups start.

INDEX MATCH is two formulas in one. MATCH finds the position of the ID in the list, and INDEX returns the item at that position from another column. Copy the MATCH part into a spare cell and look at what it returns. Here 4401 is the 4th ID in A2:A10, so MATCH returns 4.

Then read the result like this. #N/A from MATCH: the value is not found exactly, see causes 1 to 3. A number from MATCH but the wrong product: the INDEX range is shifted, see cause 4. #REF! from the full formula: INDEX was asked for a row or column it does not have, see cause 5.

Cause 1: the 0 is missing from MATCH

MATCH has three arguments, and the third one is the trap. Leave it out and Excel uses match_type 1, which assumes the list is sorted from smallest to largest and returns the position of the largest value that is less than or equal to the one you want. On an unsorted ID list like this one, =INDEX(B2:B10,MATCH(F2,A2:A10)) can return a different item's name, or #N/A, depending on how the IDs happen to be ordered.

The wrong-product case is the dangerous one, because nothing looks broken. Add 0 as the third argument for every ID, name or code lookup. Keep 1 only for tier tables sorted ascending, such as discount bands or tax brackets, where you really want the nearest lower value.

XMATCH and XLOOKUP (Excel 365 and 2021 and later) default to an exact match, so they do not have this problem.

Cause 2: a number looking for text (or the reverse)

F3 holds the number 4452. In the list, A4 holds 4452 too, but stored as text: it came in from the supplier file and sits on the left of the cell with a small green triangle. Excel treats the number 4452 and the text "4452" as different values, so MATCH finds nothing.

Check both sides: =ISTEXT(A4) is TRUE, and =A4=F3 is FALSE even though they look the same. As a quick patch, MATCH(F3&"",A2:A10,0) turns the lookup value into text and returns USB-C hub. For the opposite case, F4 holds "4387" as text while A3 is a real number, so MATCH(--F4,A2:A10,0) converts it and returns Monitor arm.

Those patches only work for the rows with the same type, so the real fix is the data. =SUMPRODUCT(--ISTEXT(A2:A10)) counts the text IDs (1 here). Select the ID column, click the warning icon and choose Convert to Number, or use Data > Text to Columns > Finish.

Cause 3: a hidden space

G2 asks for the price of "Cable tray", but B6 holds "Cable tray " with a space at the end. =LEN(B6) returns 11 for a 10-character name, which gives it away. Trailing spaces come from exports, copy-paste and people pressing space before Enter.

The cleanest fix is to clean the column: a helper column with =TRIM(B2), filled down and pasted back as values. Inside the formula, =INDEX(D2:D10,MATCH(G2,TRIM(B2:B10),0)) trims the list on the fly and returns 18.75. In Excel 2019 and older, confirm that version with Ctrl+Shift+Enter, because TRIM on a range makes it an array formula.

Two things that are not the cause: MATCH ignores upper and lower case, so "desk lamp" still finds Desk lamp. But it does treat * and ? in the lookup value as wildcards, so a code like "A*12" can match a different code. Put a tilde before them ("A~*12") to search for the real character.

Cause 4: INDEX and MATCH start on different rows

This one gives a wrong answer with no error. The INDEX range starts at B1, the header row, while the MATCH range starts at A2. MATCH still returns 4, and INDEX takes the 4th cell of B1:B10, which is B4. So ID 4401 returns USB-C hub, the product one row above the right one.

It usually happens when you select the INDEX column with the header and the MATCH column without it. The rule: both ranges start on the same row and end on the same row, for example B2:B10 and A2:A10. Read the two ranges side by side in the formula bar before you trust the result.

Cause 5: #REF! from ranges of different sizes

The list grew from 7 to 9 items and someone extended the MATCH range to A2:A10 but left INDEX on B2:B8. Desk mat (4444) is the 9th ID, so MATCH returns 9, and INDEX has only 7 cells to choose from. Asking INDEX for a row past the end of its range gives #REF!. The reverse mistake, MATCH on A2:A8 and INDEX on B2:B10, gives #N/A for the two new items, because MATCH never looks at them.

The same thing happens across columns in a two-way lookup. =INDEX(B2:D10,MATCH(F2,A2:A10,0),MATCH("Price",A1:D1,0)) gives #REF!: Price is the 4th header in A1:D1, but B2:D10 only has 3 columns. Start both on column A, as in =INDEX(A2:D10,MATCH(F2,A2:A10,0),MATCH("Price",A1:D1,0)), and the result is 52. Also, the MATCH range must be one row or one column: MATCH(F2,A2:B10,0) gives #N/A.

To stop the ranges drifting, turn the list into a table (Insert > Table) and use column names, which grow with the data. When you copy a formula down, lock the ranges with $ ($A$2:$A$10, press F4), otherwise they slide down a row at a time and the top items drop out.