Excel INDEX MATCH #N/A Error? Common Causes and Solutions

Errors & fixes · Updated November 19, 2026

An #N/A error in an INDEX MATCH formula usually means MATCH cannot find the lookup value in its search range. Check that the value exists, use 0 for an exact match, and make sure the lookup value and source data have the same type and spelling; the examples below show how to diagnose and fix the problem.

What #N/A means in INDEX MATCH

INDEX MATCH is a two-function lookup: MATCH finds a value’s position in a range, and INDEX returns the value at that position from another range. If MATCH cannot find the requested value using its selected match mode, it returns #N/A. Because INDEX depends on that position, the combined formula displays the same error.

For example, suppose column A contains employee names and column B contains departments. If the lookup cell contains a name that is not in column A, an exact-match formula cannot return a department. The error is not necessarily evidence that Excel is broken; it is a clue that the lookup step did not find a suitable match. Sometimes the values look identical on screen but differ underneath, which is why checking data types and hidden spaces matters.

How the formula works

With employee names in A2:A6, departments in B2:B6, and a name to find in E2, this formula returns the department for an exact name match:

With names in A2:A6, departments in B2:B6, and the lookup name in E2, use:

=INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0))

If E2 contains Cara and A4 contains Cara, MATCH returns 3 because Cara is the third item in A2:A6. INDEX then returns the third item in B2:B6, which is Design. The row number is relative to the selected range, not the worksheet row number: the third item is in worksheet row 4, but its position within A2:A6 is 3.

The final argument in MATCH matters. A value of 0 requests an exact match, regardless of whether the lookup range is sorted. If you omit that argument, MATCH uses approximate matching by default, which assumes the range is sorted in ascending order. That can return an unexpected position rather than the exact result you intended. For ordinary ID, name, and category lookups, explicitly using 0 is the safer choice.

Check the lookup value and ranges first

Start with the simplest checks before changing the formula:

  • Confirm the value in the lookup cell is actually present in the lookup range. Check for a spelling difference, extra character, or a different ID.
  • Confirm that MATCH searches the column or row containing the lookup values, not the return column.
  • Check that the lookup range and return range cover corresponding records and have the same number of items. In the example, both ranges run from row 2 to row 6.
  • Make sure the formula points to the intended worksheet and workbook. A reference to an older list or a shifted range can make a real value appear missing.
  • Look for filters or hidden rows only as a visual aid: MATCH can still search hidden and filtered-out cells in its referenced range.

To inspect the calculation, select the formula cell and use Formulas > Evaluate Formula on Excel for Microsoft 365 for Windows. Step through the expression to see what MATCH evaluates and whether it returns a position or #N/A. On Mac, the Evaluate Formula command is also available on the Formulas tab, though ribbon placement can vary slightly by window size. You can also select the MATCH portion in the formula bar and press F9 to display its result, but press Esc afterward so you do not replace the formula with that calculated value.

Common reasons an exact match fails

Text and numbers are different types. A cell containing the number 104 is not always equivalent to a cell containing the text string 104. This often happens when data is imported from a website, CSV file, or another system. A related issue is an ID with leading zeros: 00104 may be stored as text while 104 is stored as a number. Decide which representation is correct for the data, then make both the lookup value and source values consistent. Applying a number format alone changes how a number looks; it does not necessarily convert text to a number or restore zeros that were discarded.

Extra or nonprinting characters. A trailing space in a source value, or a leading space in the lookup cell, makes the strings different. Imported data can also include nonbreaking spaces or other characters that are difficult to see. Normal spaces and unusual spaces may require different cleanup methods.

Different dates or formats. Excel stores dates as serial numbers, while an imported date may be plain text. Two cells can display the same date but have different underlying values. Check whether both are genuine Excel dates, and compare their values or formats. The same issue can affect decimal values imported with different regional conventions.

The lookup range has moved. If a formula is copied down without fixed references, its search and return ranges may shift. Dollar signs, as in $A$2:$A$6, keep those ranges fixed when copying. Also check for inserted rows or a data list that has grown beyond the range used by the formula.

Approximate matching is being used unintentionally. Omitting MATCH’s third argument does not mean exact match. With unsorted values, approximate matching can behave unpredictably for a user expecting an exact lookup. Add the explicit 0 match type.

Fix spaces and inconsistent imported text

If a value has ordinary leading or trailing spaces, TRIM can clean it. With a source name in A2, put this formula in a helper column and fill it down before matching against the cleaned values:

With the original text in A2, use this helper formula in another column:

=TRIM(A2)

TRIM removes extra ordinary spaces from text, but it does not remove every kind of invisible character. CLEAN removes many nonprinting ASCII characters. If an imported string contains a nonbreaking space, a common cleanup step is to replace that character with a normal space before trimming.

With imported text in A2, use this formula in a helper column to replace a nonbreaking space and trim the result:

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

Fill the helper formula down for the source list, and clean the lookup value in the same way. Then point MATCH at the cleaned helper range. Cleaning only the lookup cell will not help if the unwanted character is in the source list. Keep the original data until you have checked the cleaned results; cleanup can change text, and CLEAN does not remove every possible Unicode character.

Use error handling without hiding a bad lookup

If a missing record is an expected outcome, you can replace the visible error with a clear message. With names in A2:A6, departments in B2:B6, and the lookup name in E2, use:

=IFNA(INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0)),"No matching employee")

For E2 containing a name absent from A2:A6, the result is No matching employee. IFNA catches #N/A specifically. This is usually more informative than IFERROR, which can also conceal unrelated problems such as a broken reference or a misspelled function. Error handling does not repair the lookup: if a match should exist, investigate the data and formula rather than accepting the message as the final answer.

Other details that can affect the result

Exact MATCH is not case-sensitive. For instance, it normally treats Alex and ALEX as the same text for a match. It also returns the position of the first matching item when the lookup value occurs more than once. If duplicate IDs exist, INDEX MATCH will return the first corresponding result, so check whether the lookup field is supposed to be unique.

Wildcards are another detail: with exact match mode, MATCH can interpret an asterisk as a sequence of characters and a question mark as one character. If you need to search for a literal asterisk or question mark, put a tilde before the character in the lookup text. If the error remains confusing, test the MATCH part alone in a spare cell; that separates a missing-match problem from an issue with the INDEX range.

INDEX MATCH compared with XLOOKUP

For a straightforward lookup, XLOOKUP can be easier to read because it accepts the lookup range and return range directly and uses exact match by default. It is available in Microsoft 365 and Excel 2021 or later. With names in A2:A6, departments in B2:B6, and the lookup name in E2, this returns the department or a message when there is no match:

=XLOOKUP(E2,A2:A6,B2:B6,"No matching employee")

For E2 equal to Cara in the example data, the result is Design; an absent name returns No matching employee. INDEX MATCH remains useful in older Excel versions and can be convenient when a formula needs a separately calculated position. Changing to XLOOKUP will not fix inconsistent source data, though: text-number mismatches and hidden characters can still prevent a match.

Frequently Asked Questions

Why does INDEX MATCH return #N/A when the value is visible?
It may only look identical. Check whether one value is text and the other is numeric, and look for leading, trailing, or nonprinting spaces. Also confirm that the formula searches the correct range and uses exact match mode if that is what you need.

Does MATCH need 0 for an exact match?
Yes. In the third argument of MATCH, 0 specifies an exact match. Leaving that argument out selects approximate matching, which expects an ascending-sorted lookup range and may not give the result you expect for unsorted data.

Will INDEX MATCH find a value if the letters use different capitalization?
Yes, a standard exact MATCH is not case-sensitive. If capitalization is the only difference, it normally will not cause #N/A. Spaces and other characters can still make otherwise similar text different.

Why does my formula work for some rows but return #N/A for others?
Those records may have different data quality or may be outside the referenced range. Compare a working and failing value, including its data type and hidden characters, and check that copied formulas still point to the complete source list.

Should I use IFERROR to remove #N/A?
Use IFNA when the specific issue you want to handle is a missing match. IFERROR catches many kinds of errors and may hide a separate formula problem. Neither function finds or repairs the missing value, so verify the result when the record should exist.

Related

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.