INDEX MATCH Not Working? Fix Common Lookup Errors

If INDEX MATCH works for some values but not others, the usual cause is that Excel sees the lookup value and the supposed match as different, or the MATCH arguments are set incorrectly. Start with an exact-match formula, check the lookup range and return range, then investigate text-versus-number differences, extra spaces, and other hidden characters.

How INDEX MATCH is supposed to work

INDEX returns a value from a range at a position you specify. MATCH finds the position of a value in a separate range. Combined, they let you search one column and return a related value from another, even when the return column is to the left of the lookup column.

With product codes in A2:A10, prices in C2:C10, and the code to find in E2, use:

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

The final 0 tells MATCH to look for an exact match. If the value in E2 is found in A2:A10, MATCH returns its position within that range; INDEX uses the same position in C2:C10. For example, if the matching code is in A4, it is the third item in A2:A10, so INDEX returns the third item in C2:C10, which is C4.

In Excel, the 0 argument is important. If you omit the match type, MATCH uses approximate matching, which can return an unexpected result when the data is not sorted as required. In most lookup tasks where a code, name, or ID must match exactly, use 0.

Check the formula structure and ranges

A formula can look plausible while pointing at the wrong cells. Confirm that the first INDEX argument is the range containing the values you want returned, and that the second MATCH argument is the range Excel should search. These ranges need to cover corresponding rows in the same order. If one range starts on row 2 and the other starts on row 3, the result can be offset even when MATCH finds the correct position.

Check that the lookup value is the intended cell and has not shifted after copying the formula.
Check that the lookup range contains the value you expect to find, including the first and last relevant rows.
Check that the return range is aligned with the lookup range and has the same number of rows.
Use dollar signs to lock ranges when copying the formula down, while leaving the lookup cell relative if it should change by row.

In Microsoft 365 for Windows, select the formula cell and inspect the formula bar. You can also use Formulas > Evaluate Formula to follow how Excel evaluates the expression. Press F4 while editing a cell reference to cycle through relative and absolute reference styles. On Mac, Command+T typically changes reference style while editing a formula; keyboard settings and Excel versions can affect the shortcut.

Look for numbers stored as text

One of the most common reasons a lookup succeeds on some entries and fails on others is inconsistent data type. A cell containing the numeric value 123 is not necessarily equivalent to a cell containing the text string 123. The two may look identical on the worksheet, but an exact MATCH can treat them as different. This often happens after copying data from a website, a text file, or another system.

To inspect a suspected value, compare its type or test it with a simple check. With a suspected lookup value in E2, =ISNUMBER(E2) returns TRUE for a numeric value and FALSE for text. If the IDs are meant to be numbers, convert the source data consistently using Data > Text to Columns and finish the wizard, or use the warning icon’s Convert to Number option when it appears. Avoid converting identifiers that can legitimately start with zero, such as postal codes or account codes; those are often best stored consistently as text.

Formatting a text value as Number does not necessarily convert it into a numeric value. Likewise, using VALUE on a code with leading zeros can change its meaning. Decide whether the field is a quantity or an identifier, then make both lookup and source columns use the same representation.

Find extra spaces and invisible characters

Text can appear identical and still differ because of leading spaces, trailing spaces, repeated spaces, or nonprinting characters. A copied name might contain a trailing space, while a product code from a web page might contain a nonbreaking space. MATCH sees those characters as part of the text, so an exact comparison fails.

With the suspect text in A2, =LEN(A2) counts its characters. Compare that result with the length you expect or with a known-good entry. TRIM removes ordinary leading and trailing spaces and reduces repeated ordinary spaces between words. With the suspect text in A2, =TRIM(A2) creates a cleaned version in a helper cell. Fill the helper formula down, then copy the results and use Paste Special > Values if you want to replace the original data after checking it.

TRIM does not remove every invisible character. In particular, nonbreaking spaces imported from web pages may remain. With the suspect value in A2, =SUBSTITUTE(A2,CHAR(160)," ") replaces nonbreaking spaces with ordinary spaces. You can wrap that cleaned result in TRIM if the text also has ordinary excess spaces. Apply the same cleaning approach to both the lookup value and the lookup column; cleaning only one side may leave the mismatch in place.

Understand #N/A, wrong results, and other errors

#N/A usually means MATCH did not find the requested value using the stated match type. It does not automatically mean the value is absent from the visible list: the value may differ by type, spaces, punctuation, or characters, or the lookup range may not include the row. Check the formula’s ranges and inspect the source data before deciding that the record is missing.

A wrong value without an error often points to approximate matching, an offset range, or duplicate lookup values. With match type 0, MATCH returns the position of the first exact occurrence. If the lookup value appears more than once, INDEX MATCH returns the associated value from the first occurrence, not all matching records. Sort order is not needed for exact matching.

#REF! commonly indicates an invalid reference, such as a deleted range or a formula that has been edited to refer outside the intended cells. #VALUE! can result from incompatible arguments or a more complicated formula problem. For these errors, select the formula and verify each range and argument rather than treating them as ordinary “not found” cases.

Wrapping the lookup in IFERROR can make a report look cleaner, but it can also hide a broken reference or another real problem. During troubleshooting, keep the original error visible. Once the formula is verified, you may choose to show a message instead. With the product-code layout described earlier, =IFERROR(INDEX($C$2:$C$10,MATCH(E2,$A$2:$A$10,0)),"Not found") displays Not found for any error, so use it only if masking all errors is acceptable for that report.

Check duplicates, wildcards, and case sensitivity

Exact text matching in MATCH is not case-sensitive. For example, ABC and abc are treated as the same text for an ordinary exact MATCH. If capitalization is the only difference, it will not explain why the standard formula fails. If capitalization must distinguish records, a case-sensitive approach needs a different formula design.

Duplicates matter because INDEX MATCH returns one result: the first match in the lookup range. If multiple rows have the same name or code, add another identifying field or use a method that can return multiple records. For a two-condition lookup in Excel for Microsoft 365, with IDs in A2:A10, regions in B2:B10, return values in C2:C10, the ID to find in E2, and the region in F2, use:

=INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10=E2)*($B$2:$B$10=F2),0))

This finds the row where both conditions are true. If no row satisfies both conditions, it returns #N/A. In current Microsoft 365 Excel, this formula can handle the array calculation directly; older Excel versions may require different array-entry behavior.

MATCH with 0 can also use wildcards in text criteria: an asterisk represents any number of characters and a question mark represents one character. That can help when you intentionally want a partial match, but it can also match the wrong record if several values share the same beginning. For example, searching for a short prefix is not a reliable substitute for cleaning an inconsistent ID. Use wildcards only when partial matching is the actual goal.

A practical troubleshooting sequence

When only certain values fail, work through the checks in an order that isolates the cause instead of rewriting the formula immediately:

Confirm the formula uses 0 as MATCH’s third argument for an exact match.
Copy the failing lookup value and search the lookup column directly. Confirm it is inside the referenced range, not merely somewhere else on the sheet.
Compare the data types of the lookup value and source value. Check for number stored as text or IDs with leading zeros.
Check text length, spaces, and copied characters. Test a cleaned helper column rather than changing the original data blindly.
Verify that lookup and return ranges start and end on corresponding rows and remain fixed when the formula is filled down.
Check for duplicates and confirm that the first matching record is the one you expect.
Use Formulas > Evaluate Formula to identify the step that produces an unexpected position or error.

To see whether the lookup value occurs in the source range, with the lookup value in E2 and source values in A2:A10, =COUNTIF($A$2:$A$10,E2) counts matching entries. A result of 0 is a clue to investigate, not definitive proof that no visually similar value exists: criteria characters and data inconsistencies can affect what you think should match. A result above 1 signals duplicates, so check which occurrence the formula will return.

INDEX MATCH compared with XLOOKUP

INDEX MATCH remains useful in Excel because it works across a wide range of Excel versions and can return values from a column on either side of the lookup column. It also makes the search range and return range explicit, which can be helpful in established workbooks.

XLOOKUP is a more direct option in Microsoft 365 and Excel 2021 or later. It combines the lookup and return ranges in one function and uses exact matching by default. It does not, however, fix mismatched data types, hidden spaces, or missing records; those data-quality problems can affect any lookup method. If a workbook must be opened in an older Excel version, check compatibility before replacing INDEX MATCH with XLOOKUP.

Frequently Asked Questions

Why does INDEX MATCH return #N/A when the value is visibly there?
Excel may see a text value and a number as different, or the visible text may include spaces or hidden characters. Also verify that the value is inside the MATCH range and that the formula uses exact matching with 0.

Does MATCH need the lookup column sorted?
Not when you use 0 for an exact match. Sorting requirements apply to approximate match modes, not the usual exact-match lookup shown here.

Why does INDEX MATCH return the wrong row?
Check for an omitted 0, misaligned ranges, or duplicate lookup values. With an exact match and duplicates, MATCH returns the first occurrence in the lookup range.

Can INDEX MATCH search to the left?
Yes. MATCH can search one range while INDEX returns from a separate range to its left or right, provided the ranges are aligned and contain the same number of rows.

Will TRIM fix every hidden-space problem?
No. TRIM handles ordinary spaces but does not remove every nonprinting character, including nonbreaking spaces in many imported datasets. Use a suitable cleaning step on both the lookup value and source values, then check the result.

Should I hide #N/A with IFERROR?
Only after confirming the formula works and deciding how a genuine missing value should appear. IFERROR can also hide errors unrelated to a missing match, making problems harder to diagnose.

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.