# 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

=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, and retry the formula.

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