# 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)

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.

## 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.