How to correct a #N/A error in INDEX/MATCH functions

The topic describes the most common reasons for #N/A error to appear in the INDEX and MATCH functions.

Note: If you want the INDEX/MATCH function to return a meaningful value instead of #N/A, use the IFERROR function and then nest the INDEX and MATCH functions inside of it. Replacing #N/A with your own value only covers up the error, and does not resolve it, so before using IFERROR, make sure the formula works the way you want.

Problem: There is no data to match

When the MATCH function does not find the lookup value in the lookup array, it throws the #N/A error.

If you believe that the data is present in the spreadsheet, but MATCH is unable to locate it, it may be because:

  • The cell has unexpected characters or hidden spaces.

  • The cell may not be formatted as a correct data type. For example, the cell has numerical values, but it may be formatted as Text.

Solution: To remove unexpected characters or hidden spaces, use the CLEAN or TRIM function respectively. Also verify if the cells are formatted as correct data types.

You have used an array formula without pressing Ctrl+Shift+Enter

When you use an array in INDEX, MATCH, or INDEX/MATCH combination, you have to press Ctrl+Shift+Enter. Excel will automatically wrap the formula in curly braces {}. If you try to enter them yourself, Excel will display the formula as text.

Problem: There is an inconsistency in the match type and the sorting order of the data

When you use MATCH, there should be a consistency between the value in the match_type argument and the sorting order of the values in the lookup array. If the syntax deviates from the following rules, you will see the #N/A error.

  • If match_type is 1 or not specified, the values in lookup_array should be in an ascending order. For example, -2, -1, 0 , 1 , 2…, A, B, C…, FALSE, TRUE, to name a few.

  • If match_type is -1, the values in lookup_array should be in a descending order.

In the following example, the MATCH function is


#NA error in MATCH because of incorrect sort order

The match_type argument in the syntax is set to -1, which means that the order of values in B2:B10 should be in descending order for the formula to work. But the values are in ascending order, and that causes the #N/A error.

Solution: Either change the match_type argument to 1, or sort the table in descending format, and retry the formula.

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.

See Also

How to correct a #N/A error

How to use the INDEX and MATCH worksheet functions with multiple criteria in Excel

INDEX function

MATCH function

Overview of formulas in Excel

How to avoid broken formulas

Use error checking to detect errors in formulas

All Excel functions (alphabetical)

All Excel functions (by category)

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!