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.

**Table of Contents**hide

## Download Practice Book

Download the practice book here

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

`=INDEX(range,(MATCH (lookup_value, lookup_array, [match_type]))`

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.

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

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:

`=INDEX(C5:C9,MATCH(F5,B5:B9))`

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:

`=INDEX(C5:C9,MATCH(F5,B5:B9,0))`

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

**Read More:** **How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results**

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

**Similar Readings**

**Excel INDEX MATCH to Return Multiple Values in One Cell****Excel INDEX-MATCH Formula to Return Multiple Values Horizontally****INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)****INDEX MATCH vs VLOOKUP Function (9 Examples)****Index Function to Match and Return Multiple Values Vertically in Excel**

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

`=INDEX(EmployeeID[ID],MATCH(F5,EmployeeID[ID],0))`

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:

`=INDEX(EmployeeID[Employee],MATCH(F5,EmployeeID[ID],0))`

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

**Read More:** **Excel INDEX MATCH with Multiple Criteria (4 Suitable Examples)**

### 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**:

`=INDEX(C5:C9,MATCH(F5,D5:D9,0))`

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:

`=INDEX(C5:C9,MATCH(F5,B5:B9,0))`

- 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:

`=INDEX(C$5:C$9,MATCH(F5,B$5:B$9,0))`

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

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

**IF with INDEX-MATCH in Excel (3 Suitable Approaches)****INDEX MATCH with Multiple Criteria in a Different Sheet (2 Ways)****INDEX, MATCH and MAX with Multiple Criteria in Excel****How to Match Multiple Criteria from Different Arrays in Excel****INDEX-MATCH Formula to Find Minimum Value in Excel (4 Suitable Ways)****Index Match with Multiple Matches in Excel (5 Methods)**

Hi there.

I’ve got a problem that I don’t understand how it happens.

I hope if you could give me a little mercy here.

I’ve read so many articles about it, but it turns out your one has lot of details in it.

I’m using index, match functions to find a cerntain value from other sheet(using importrange).

but the thing is that when i use the formula, the first value does not come out, it shows n/a

for example

this is data sheet

1 a A 22/8/1

2 b B 22/8/1

3 c C 22/8/1

this is my formula to find the ‘A’,’B’,’C’

to find ‘A’ =iferror((index(‘datasheet’!c:c),match(1,(‘datasheet’!a:a=”1″)*(‘datasheet!b:b=”a”)*(‘datasheet’!d:d=”22/8/1″),0))),”-“)

-> it shows “-”

but use it to find ‘B’

=iferror((index(‘datasheet’!c:c),match(1,(‘datasheet’!a:a=”2″)*(‘datasheet!b:b=”b”)*(‘datasheet’!d:d=”22/8/1″),0))),”-“)

-> it shows ‘B’ CORRECTLY!

I DON’T KNOW WHY THE FIRST FORMULA CAN NOT FIND THE ANSWER..?

PLEASE HELP ME

Hi JUN,

Thanks for your comment. I assume you are having this problem because Excel considers

1(‘dataset’!=”1″)as a number. You don’t need to add the double quotation symbol for that part. So, an error occurs and it is showing “–” instead of “A“. You can use the formula below to get “A“:`=IFERROR((INDEX(datasheet!C:C,MATCH(1,(datasheet!A:A=1)*(datasheet!B:B="a")*(datasheet!D:D="22/8/1"),0))),"-")`

To get “

B“, use the formula below:`=IFERROR((INDEX(datasheet!C:C,MATCH(1,(datasheet!A:A=2)*(datasheet!B:B="b")*(datasheet!D:D="22/8/1"),0))),"-")`

And to get “

C“, you can use the formula below:`=IFERROR((INDEX(datasheet!C:C,MATCH(1,(datasheet!A:A=3)*(datasheet!B:B="c")*(datasheet!D:D="22/8/1"),0))),"-")`

For your convenience, I have attached the excel file below with the formulas that I have used. I have used the formulas in

Excel 365.Download Excel FileI hope this will help you to solve your problem. If you still find any problem in your excel file, then you can send it to [email protected]. I will take a look and email you the solution.

Thanks!