INDEX MATCH #N/A Error: Causes and Fixes

In Excel, an INDEX MATCH formula that returns #N/A usually means MATCH cannot find the lookup value in the range it was told to search. Check that the lookup value and search range truly match, then use an exact-match argument of 0; also check for extra spaces, different data types, or a missing source value.

What #N/A means in INDEX MATCH

INDEX and MATCH do different jobs. MATCH searches a range and returns the position of a value within that range. INDEX uses that position to return a value from another range. If MATCH cannot locate the requested value, it returns #N/A, and the error passes through the larger formula.

For example, suppose a worksheet lists product codes in A2:A5 and prices in B2:B5, and the code to find is in E2. With product codes A102, A103, A104, and A105 in that order, searching for A104 should return the third price in the price range. If E2 contains A106, MATCH has no position to return, so the complete lookup displays #N/A.

This is different from the Excel function NA(), which deliberately returns the #N/A error value. A cell in the return range might itself contain #N/A; in that case, MATCH may have succeeded, but INDEX is returning an error already present in the source data. Finding which stage is responsible is the first useful diagnostic step.

How the lookup formula works

The basic pattern uses one vertical range for the values to search and a second, equally aligned range for the values to return. The lookup value can be in a cell or written directly into the formula. For this example, assume product codes are in A2:A5, corresponding prices are in B2:B5, and the code being searched for is in E2:

=INDEX(B2:B5,MATCH(E2,A2:A5,0))

The final 0 tells MATCH to look for an exact match. If E2 contains A104 and the prices in B2:B5 are 12, 15, 18, and 21, the formula returns 18: A104 is the third code in the search range, so INDEX returns the third price. The ranges must be aligned in the same order and have the same number of rows. If they are offset, the result could be incorrect even if the formula does not show an error.

To isolate the search step, use MATCH on its own. With the same layout—codes in A2:A5 and the lookup code in E2—this formula returns 3 when E2 contains A104, or #N/A when the code is absent:

=MATCH(E2,A2:A5,0)

If this standalone check returns a position but the full formula still displays #N/A, inspect the corresponding cell in the return range. It may already contain an error. If MATCH itself returns #N/A, focus on the lookup value and the range being searched.

Common reasons MATCH cannot find a value

The value is not in the selected range. A formula may search A2:A100 even though the matching record is in A101, or it may point to the wrong worksheet or column. Confirm the actual location of the expected item and expand or correct the search range.
The formula is using approximate matching. MATCH uses approximate matching when its third argument is omitted. For a normal exact lookup, include 0 explicitly. Approximate match types have ordering requirements and are generally a poor choice for a list of identifiers.
Text and numbers differ. A number stored as text, such as text-formatted 104, may not match the numeric value 104. Codes with leading zeros are especially sensitive: text 00104 is not necessarily the same identifier as the number 104.
There are extra or invisible characters. A trailing space, a nonbreaking space copied from a website, or another hidden character can make two values look identical while Excel treats them as different. A normal space before or after a value is enough to prevent an exact match.
The search range is wrong or misaligned. MATCH may be pointed at a neighboring column, a partial list, or a range with different rows from the INDEX return range. Check both references, especially when copying a formula down a column.
The source data contains errors. If the lookup succeeds but the selected return cell contains #N/A, INDEX will show that error. Inspect the source cell rather than changing the search criteria.

Exact text matching in MATCH is not case-sensitive: for example, a search for abc can match ABC. However, when the match type is 0, the wildcard characters * and ? can have special meaning in text searches. A tilde can be used to escape a wildcard when you need to search for the character itself. These details matter for product names or labels that literally contain wildcard characters, but are rarely the cause of an error in ordinary numeric lookups.

A practical troubleshooting sequence

Work from the simplest check to the more involved ones. This helps distinguish a genuinely missing value from a formula-reference or data-cleaning problem.

Confirm the lookup value. Select the lookup cell and inspect its contents in the formula bar. Check for a typo, unexpected space, or missing leading zero.
Search the intended range. Verify that the exact record is inside the cells MATCH searches, not merely somewhere else on the sheet. If needed, use Find to locate a visible version of the value and confirm its column.
Test MATCH by itself. Temporarily enter a standalone exact-match formula. A number means the search succeeded; #N/A means the lookup value was not matched in that range.
Check the data type. Compare whether both values are numbers or both are text. A green warning indicator can signal a number stored as text, but the indicator is not present in every case. Do not convert identifiers blindly if leading zeros are meaningful.
Inspect spaces and copied characters. Compare the source and lookup values in the formula bar. For imported data, clean a helper column and perform the lookup against the cleaned values on both sides.
Recheck reference alignment. Ensure the INDEX range and MATCH range cover corresponding records in the same order. Use absolute references where appropriate if copying the formula, so the lookup ranges do not slide.

For the standalone check above, a successful result is the item’s position within the search range, not its worksheet row number. If A2:A5 contains the match in A4, MATCH returns 3 because A4 is the third cell in that selected range. That distinction can help when verifying that INDEX is retrieving the intended row.

Fixing spaces and inconsistent imported data

TRIM removes ordinary leading and trailing spaces and reduces repeated internal spaces; CLEAN removes many nonprinting characters. With the value to clean in A2, this helper formula creates a cleaned version in another cell:

=TRIM(CLEAN(A2))

Fill the helper formula down for the source list, and apply the same cleanup to the lookup value. Then point MATCH to the cleaned lookup column and compare it with the cleaned lookup cell. Cleaning only one side will not resolve a difference that remains on the other side. Keep the original data until you have checked that the cleaned values preserve meaningful distinctions.

Some web exports contain nonbreaking spaces, which TRIM may not remove. If the unwanted character is a nonbreaking space, commonly represented by character 160, a helper formula can replace it with an ordinary space before cleaning. With the text to repair in A2, use:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

This formula handles that specific character; it does not guarantee that every unusual Unicode character has been removed. For values that look identical but still fail to match, check the origin and encoding of the imported data. For numbers stored as text, choose a conversion method that preserves the intended meaning, especially for codes with leading zeros.

Returning a helpful message instead of #N/A

Once the lookup logic is correct, IFNA can replace a not-found result with a clear message. With codes in A2:A5, prices in B2:B5, and the search code in E2, this formula displays Not found when MATCH returns #N/A:

=IFNA(INDEX(B2:B5,MATCH(E2,A2:A5,0)),"Not found")

For example, if E2 contains A106 and that code is not in A2:A5, the displayed result is Not found. If E2 contains A104 and the corresponding price is 18, the result remains 18. IFNA is preferable to hiding every error when the specific issue you want to handle is a missing lookup value; other errors can still point to a broken reference or another formula problem.

Showing a message is not the same as fixing a lookup. If the missing item should exist, an error-handling wrapper can conceal a spelling problem, an incomplete range, or dirty data. During troubleshooting, remove the wrapper or test MATCH separately, then add a friendly message only when a missing match is an expected outcome.

INDEX MATCH compared with other lookup options

INDEX MATCH remains useful for combining a search with a return range and for adapting formulas to different layouts. It can look up a value in a column and return a related value from another column, including one to the left of the search column. Its main weakness for many users is that the two ranges and the MATCH arguments must be set up correctly.

XLOOKUP is a newer alternative in Microsoft 365 and Excel 2021 or later. It accepts separate lookup and return ranges and includes an optional not-found result, which can make a simple lookup easier to read. For example, with codes in A2:A5, prices in B2:B5, and the requested code in E2, this returns the matching price or a message:

=XLOOKUP(E2,A2:A5,B2:B5,"Not found")

If E2 is A104 and its price is 18, the result is 18; if it is A106, the result is Not found. XLOOKUP is not available in every older Excel edition, so INDEX MATCH can be useful when sharing workbooks with users on older versions. Changing functions will not solve the underlying problem if the lookup value is absent, has a different type, or contains hidden characters.

Frequently Asked Questions

Why does INDEX MATCH return #N/A when the value looks identical?
Excel may be comparing text with a number, or one value may include a trailing space or invisible character. Test MATCH alone, inspect the formula bar, and check both values’ data types and imported characters.

Should I leave out the 0 in MATCH?
No, not for a standard exact lookup. When the third argument is omitted, MATCH uses an approximate-match mode that depends on the data being ordered. Use 0 when searching for an exact item such as an ID or product code.

Can INDEX MATCH return #N/A even if MATCH finds the value?
Yes. INDEX can return #N/A if the selected cell in the return range already contains that error. Check the standalone MATCH result and then inspect the corresponding return cell.

Does MATCH distinguish uppercase and lowercase?
No. MATCH is not case-sensitive for ordinary text matching. If capitalization must determine a match, a case-sensitive formula needs a different approach; changing letter case alone will not explain a typical INDEX MATCH #N/A.

Is it safe to use IFNA to hide the error?
It is appropriate when an item may legitimately be absent and a message is more useful than an error. First confirm the formula and source data, because IFNA can otherwise make an accidental missing match less noticeable.

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.