[Fixed!] INDEX MATCH Not Returning Correct Value in Excel (5 Reasons)

Nowadays, we often use INDEX MATCH to look up a value in a dataset. It provides us with many advantages. But it has disadvantages too. Sometimes, INDEX MATCH does not return the correct value in Excel. This can happen due to various reasons. In this article, we will discuss 5 possible reasons for INDEX MATCH not returning the correct value and their solutions. So, without further ado, let’s start the discussion.

INDEX Function in Excel

The INDEX function returns the value of a cell at the intersection of a particular row and column in an array or range.

• Syntax

INDEX (array, row_num, [col_num], [area_num])

• Arguments

array: This is the first compulsory argument of the INDEX function. It is the constant array or the cell range.

row_num: It is the second compulsory argument and row number from the desired array.

[col_num]: It is an optional argument that represents the column number of the desired array.

[area_num]: It is also an optional argument that selects a range in a reference. Here, It returns the intersection of row_num and col_num.

MATCH Function in Excel

The MATCH function looks for a specific value in an array or range and returns the relative position of that value in the array or range.

• Syntax

MATCH (lookup_value, lookup_array, [match_type])

• Arguments

lookup_value: This is the first compulsory argument of the MATCH function. It is the value that we search in a range or array.

lookup_array: It is the second compulsory argument. It is the array where we search for the value.

[match_type]: It is an optional argument that represents the type of match we want. For example, if you want an exact match, you need to type 0 in the third argument. You can also type 1 and -1. 1 gets the greatest value which is less than or equal to the lookup_value and -1 gets the smallest value which is greater than or equal to the lookup_value.

INDEX and MATCH Combination

The combination of INDEX and MATCH functions is one of the most powerful combinations in Excel. We can use this combination to do tasks of the VLOOKUP function easily. The combination is given below:

Here, first, we use the INDEX function to look up a value in a certain range. Then, the MATCH function extracts the matched value using the given arguments.

Read More: Excel INDEX MATCH Example

INDEX MATCH Not Returning Correct Value in Excel: 5 Possible Reasons with Solutions

To explain these reasons and their solutions, we will use a dataset that contains information about the ID, Name, and Department of some employees. We will try to look up a value in this dataset using INDEX MATCH and solve the problems we may face to get the correct value.

Reason 1: INDEX MATCH Will Provide Wrong Value If Exact Match Is Not Correct

INDEX MATCH will not return the correct value if you do not provide any match type. You need to provide the exact match when you are using this formula. Suppose, we want to extract the name of the employee in Cell G5 given the ID number in Cell F5. If we change the ID number, the name of the employee will also be updated automatically. For that purpose, we have written the formula below:

In this case, the formula should have extracted Paul in Cell G5. But, it has shown Mike which is not the correct value. Let’s follow the solution below to return the correct value.

Solution: Indicate Exact Match Correctly

In the above formula, we did not use any match type in the third argument inside the MATCH function. We know there are 3 match types. To show the correct value, we need to use the Exact Match. So, to return the correct value, you need to write 0 in the third argument of the MATCH function. Let’s follow the steps below to solve the problem.

STEPS:

• In the first place, select Cell G5.
• Secondly, type the new formula instead of the previous one:

• Finally, hit Enter to see the correct result.

The only difference between this formula and the previous one is that we have used 0 to indicate the exact match in the third argument inside the MATCH function.

Reason 2: Blank Cells Can Lead INDEX MATCH Not to Return Correct Value in Excel

If we add blank cells inside the range or the lookup_array, we may see the wrong value in the desired cell. In the picture below, everything is fine with the result in Cell G5. We can see from the original dataset that 301 is the ID number of Carry.

Now, if someone inserts blank cells in the original dataset, Cell G5 will show an incorrect result.

Solution: Delete Blank Cells

If you write the correct formula and still don’t get the correct answer, then you should check for the blank cells. Follow the steps below to delete the blank cells.

STEPS:

• First of all, select the blank cell and right-click on it. A context menu will appear.
• Select Delete from the context menu.

• After that, a message box will appear. Select Shift cells up from there and click OK to proceed.

• Finally, after clicking OK, the formula will return the correct value.

Reason 3: INDEX MATCH Will Show Incorrect Value If Table and Range Don’t Correspond

When you are using a table, you need to be extra careful. Suppose, we have converted our previous dataset into a table and named it EmployeeID. Now, we write the formula below to extract the employee name from the given ID.

Here, the formula is showing the ID number instead of the employee name. The first argument of the INDEX MATCH is EmployeeID[ID]. It represents the column that has the header ID in the table named EmployeeID.

Solution: Make Corresponding Table and Range

To solve the problem, follow the steps below.

STEPS:

• First, select Cell G5 and type the formula below:

• After that, press Enter to see the correct result.

Here, you have to use EmployeeID[Employee] instead of EmployeeID[ID]. Because EmployeeID[Employee] represents the name of the employee in the original table. Also, you need to write the name of the table correctly.

Reason 4: Formula Will Not Give Correct Result If Wrong Lookup Array Is Provided

The INDEX MATCH will not give the correct result if the wrong lookup array is provided. To understand the problem, take a look at the picture below. Here, we used the formula below in Cell G5:

Here, the formula is showing the #N/A error in Cell G5 in the above picture. But, if we write IT in Cell F5, we will see a value in Cell G5. But, our desired goal was not achieved as we could not get the result by typing the ID number. It happened because we provided the wrong lookup array.

Solution: Type the Lookup Array Correctly

To solve the index match not returning the correct value problem, you need to provide the correct lookup array. We want to extract the name of the employee given the ID number. For that, we need to write the array where the lookup value is situated. In our case, our lookup value is the ID number that is situated in the B5:B9 array. So, B5:B9 is our correct lookup array. Follow the steps below to implement the solution.

STEPS:

• Select Cell G5 and type the formula:

• Then, hit Enter to see the result.

The main difference between this formula with the previous one is that we have written B5:B9 in place of D5:D9.

Reason 5: Result Will Be Different When Reference Is Not Locked

This problem occurs when you drag the INDEX MATCH formula down or across. Here, we have dragged the formula down in the picture below. We can see the correct results in Cell G6 to G8 but with errors. And in Cell G9, we can see the #N/A error. This happens when your reference is not locked. The arrays will move along the direction of the dragging and produce wrong results or errors.

Solution: Use Reference Lock Correctly in Formula

To get rid of the problem, you need to lock your reference. In Excel, we use the dollar ($) sign to lock any cell or reference. In most cases, we drag the formula down. Let’s follow the steps below to learn this solution.

STEPS:

• In the first place, select Cell G5 and type the formula below:

• After that, hit Enter to see the result.

• Finally, drag the Fill Handle down to see the correct values.

Here, you need to lock the reference carefully. If you want to drag the formula across, you need to lock the column index also. For that purpose, you need to place a dollar ($) sign before the column index also.

Things to Remember

It is very easy to solve INDEX MATCH problems when you have a small dataset. But, you need to be careful while working with a large dataset. First, you need to find the problem in your dataset and then, use the solution accordingly.

Download Practice Book

Download the practice book here

Conclusion

We have discussed 5 reasons for INDEX MATCH not returning the correct value in Excel with their solutions. We have tried to discuss the possible problems that can occur while working with the INDEX MATCH functions. Moreover, the practice book is also added at the beginning of the article. You can download it to practice more. Last of all, if you have any queries or suggestions, feel free to ask in the comment section below.

Related Articles

• INDEX-MATCH Formula to Find Minimum Value in Excel
• INDEX MATCH for Multiple Criteria in Rows and Columns in Excel
• How to Use INDIRECT, INDEX, and MATCH Functions in Excel
• How to Use INDEX MATCH with Multiple Criteria for Date Range
• Excel INDEX MATCH If Cell Contains Text
• How to Use INDEX MATCH Instead of VLOOKUP in Excel
• XLOOKUP vs INDEX-MATCH in Excel
• Excel INDEX MATCH to Return Multiple Values in One Cell
• INDEX, MATCH and MAX with Multiple Criteria in Excel