Why Is INDEX MATCH Not Working? Common Excel Fixes

INDEX MATCH usually fails because MATCH is using the wrong match type, the lookup and return ranges do not line up, or the lookup value differs from the stored data. With lookup codes in A2:A5, return values in C2:C5, and the code to find in E2, use =INDEX($C$2:$C$5,MATCH(E2,$A$2:$A$5,0)). If E2 contains P-103 and that code is in A4, the formula returns the value in C4.

How INDEX MATCH works

INDEX and MATCH divide a lookup into two jobs. MATCH searches a range and returns the position of a value within that range. INDEX uses a position to return the corresponding item from another range. In the example above, MATCH finds the position of E2 within A2:A5; INDEX then returns the value at that position in C2:C5.

This arrangement is useful because the lookup column does not have to be the leftmost column in a table. It also lets you change the lookup and return ranges independently. But that flexibility means the ranges must be set up correctly: both should cover the same number of rows, and their first rows must represent the same record. If MATCH finds the third item in its range, INDEX returns the third item in its range—not necessarily the item from the same worksheet row if the ranges start in different places.

INDEX MATCH is not one combined function with its own special lookup rules. Most problems come from how MATCH interprets the search value or how the two ranges relate to each other.

Check MATCH’s match type first

The third argument in MATCH controls how it searches. In =MATCH(E2,A2:A5,0), the zero requests an exact match. Without that argument, MATCH uses its default approximate-match behavior, which can return an unexpected position or an error if the data is not arranged as required. For text and codes, exact matching with 0 is usually the right choice.

Approximate matches are intended for cases such as finding the applicable tax band, score bracket, or commission threshold. With match type 1, the lookup range must be sorted in ascending order; with -1, it must be sorted in descending order. An unsorted list can make approximate MATCH return a misleading result. Do not omit the third argument just because a formula appears to work on one sample: it may silently return the wrong row when the data changes.

Exact match does not mean case-sensitive match. MATCH treats uppercase and lowercase text as equivalent, so ABC and abc can match. It can also interpret the wildcard characters * and ? in text lookup values: * represents any sequence of characters and ? represents one character. To search for a literal wildcard, precede it with a tilde. These details can matter when codes contain punctuation or when a search appears to match more broadly than expected.

Make sure the ranges line up

A common construction error is matching one range but indexing a different-sized or differently positioned range. For example, matching A2:A100 while indexing C1:C99 shifts the correspondence: the first match position points to C1 rather than C2. Even where the formula returns a value instead of an error, that shift can produce a plausible but incorrect answer.

Check these points in the formula bar:

The lookup range and return range have the same number of cells.
Their first cells refer to the same record, and their last cells do too.
The lookup value is being searched in the intended column, not a neighboring field.
Absolute references, shown with dollar signs, are used where ranges should stay fixed when copying the formula.

For example, if a formula is copied down and its lookup range changes from A2:A100 to A3:A101, it may still work on some rows but omit the first record. Locking the ranges, as in the opening example, prevents that particular shift. If you deliberately use a bounded range, update both ranges together when new records are added.

Look for differences in the underlying data

Two cells can look identical while containing different values. A frequent cause is a number stored as text in one place and a numeric value in another. This often happens after importing a CSV file, copying data from a website, or adding an apostrophe to force a value to be text. A code such as 00127 may also be stored as text to preserve its leading zeros; converting it to the number 127 changes the value, even if the cells look similar after formatting.

Spaces and invisible characters are another source of failed exact matches. A trailing space in a product code, a nonbreaking space copied from a web page, or a line-break character may not be obvious in the cell. TRIM removes ordinary extra spaces at the start and end of text and reduces repeated ordinary spaces, while CLEAN removes many nonprinting control characters. Neither function removes every character that may arrive in imported text; in particular, a nonbreaking space may need to be replaced explicitly.

With the suspect value in A2, this helper formula replaces nonbreaking spaces with ordinary spaces, removes common control characters, and trims ordinary extra spaces: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). If A2 contains the text ABC followed by a nonbreaking space, the helper result is ABC. Apply the same cleanup to both the lookup values and the search value, then match the cleaned columns. Keep the original data if its exact formatting or contents are important.

Dates can cause similar confusion. A date displayed as 3/4/2026 may be a real Excel date serial in one cell and text in another. Number formatting changes how a value appears; it does not convert text into a number or date. Check the source data and convert it consistently rather than relying on appearance alone.

Understand the error or wrong result

#N/A usually means MATCH did not find an exact match. Confirm that the lookup value exists in the lookup range, then investigate spelling, spaces, data type, and hidden characters. Also verify that the formula is searching the correct range. If an exact match is intended, inspect the final MATCH argument and make sure it is 0.

#REF! generally signals an invalid reference. A referenced row, column, or cell may have been deleted, or a range may have been edited incorrectly. Select the formula and inspect each colored reference in the formula bar. Rebuild the damaged range rather than masking the error.

#VALUE! can arise from invalid arguments or incompatible range shapes, particularly when using a more complex INDEX arrangement. In a straightforward vertical lookup, check that MATCH returns a usable position and that INDEX is given the intended array. A formula can also return a value without an error but still be wrong because of an approximate match, shifted ranges, duplicate keys, or a mistaken return column.

To isolate the issue, temporarily evaluate MATCH on its own using the same lookup value and lookup range. With E2 as the lookup value and A2:A5 as the lookup range, =MATCH(E2,A2:A5,0) returns 3 when E2 is found in the third cell of A2:A5. If it returns #N/A, troubleshoot the value or lookup range before examining INDEX. If it returns a position, check whether that position points to the intended item in the return range.

Use a practical troubleshooting sequence

Rather than rewriting the entire formula, test one assumption at a time. This makes it easier to distinguish a data problem from a reference problem:

Click the formula cell and verify that the formula uses the lookup value and columns you intended.
Confirm that MATCH ends with 0 for an exact lookup.
Run MATCH by itself and note the position it returns or the error it produces.
Compare the lookup value with the source cell in the formula bar, not just on the worksheet. Check for extra spaces, different data types, and hidden characters.
Compare the start and end cells of the lookup and return ranges. Make sure each matched position points to the corresponding record.
Check for duplicate lookup values. Exact MATCH returns the position of the first match it encounters, so it will not automatically select a later duplicate.
After correcting the formula or source data, test it with a known record and one value that should not be found.

Also check whether calculation is set to automatic if results are not updating after edits. In Excel for Microsoft 365 on Windows, use Formulas > Calculation Options > Automatic. If calculation is manual, press F9 to recalculate the workbook. On Mac, use Formulas > Calculation Options and choose Automatic; the available shortcut for recalculation can differ by keyboard and Excel version.

Duplicates, copied formulas, and workbook size

INDEX MATCH returns one result, not every row with a matching key. When keys are duplicated, MATCH finds the first occurrence in the searched range. If the records should be unique, check the source list for duplicates; otherwise, define which record should be returned or use a method designed to show multiple results. A formula that returns the “wrong” duplicate may be following its normal first-match behavior.

When copying a formula, relative references move while absolute references stay fixed. That is useful for changing E2 to E3 as you fill down, but not for shifting the source ranges. The dollar signs in $A$2:$A$100 and $C$2:$C$100 keep those ranges anchored. If the lookup value itself should not move, use an absolute reference for that cell too.

Very large or full-column lookup ranges can make a workbook slower, especially when many formulas recalculate. Use ranges that cover the data, or consider converting the source data to an Excel table so its references expand with added rows. Performance is not usually the explanation for a single #N/A, but it can become relevant in a large workbook with thousands of repeated lookups.

INDEX MATCH compared with XLOOKUP

In Microsoft 365, XLOOKUP can express a simple exact lookup more directly and uses exact match by default. For the same example, with lookup codes in A2:A5, return values in C2:C5, and the requested code in E2, =XLOOKUP(E2,A2:A5,C2:C5,"Not found") returns the corresponding value or the text Not found if there is no match. If E2 is P-103 and C4 contains 7, the result is 7.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it is not available in some older Excel versions. INDEX MATCH remains useful in older workbooks and offers flexibility for more complex row-and-column lookups. Switching functions will not fix inconsistent source data, hidden spaces, or an incorrect lookup column; diagnose those causes regardless of which lookup function you use.

Frequently Asked Questions

Why does INDEX MATCH return #N/A when the value is there?
It may only look as though the values are identical. Check for text-versus-number differences, leading or trailing spaces, nonbreaking spaces, and whether the formula references the column that actually contains the key. For an exact lookup, confirm MATCH uses 0.

Why does INDEX MATCH return the wrong row?
Check for mismatched range starting points, an omitted or incorrect match type, approximate matching on unsorted data, and duplicate keys. MATCH returns a position within its own range, which INDEX applies to the return range.

Does INDEX MATCH need Ctrl+Shift+Enter?
A standard single-result INDEX MATCH formula does not require Ctrl+Shift+Enter in current Excel. Some older array-formula constructions may require it, but that is not necessary for the ordinary one-column lookup described here.

Is INDEX MATCH case-sensitive?
No. MATCH is not case-sensitive for ordinary text lookups. If uppercase and lowercase distinctions matter, use a case-sensitive approach such as one built around the EXACT function, with care to handle the resulting array logic.

Why does the formula work on one row but fail when copied?
Source ranges may be shifting as the formula is filled, or a lookup value may be moving when it should remain fixed. Lock the ranges that must stay constant with dollar signs, then check each copied formula and ensure the lookup cell changes as intended.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.