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

The topic describes the most common reasons for "#N/A error" to appear are as a result of either the INDEXor MATCH functions.
 

Note: If you want either the INDEX or MATCH function to return a meaningful value instead of #N/A, use the IFERROR function and then nest the INDEX and MATCH functions within that function. Replacing #N/A with your own value only identifies the error, but does not resolve it. So, it's very important, before using IFERROR, ensure that the formula is working correctly as you intend.

Problem: There is no data to match

When the MATCH function does not find the lookup value in the lookup array, it returns 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 a combination of those two functions, it is necessary to press Ctrl-Shift-Enter on the keyboard. Excel will automatically enclose the formula within 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.

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

In the following example, the MATCH function is

=MATCH(40,B2:B10,-1)

excel match function

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. Then try it again.

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)

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×