Now and then, we have to search for specific information in our large **Excel** worksheet. But, it’s tiresome to search for it manually. A formula collaborating **the INDEX and MATCH functions** can do the amazing work of searching data very easily. It can also perform advanced lookups. In this article, we’ll show you the simple and effective ways to **Use **the **INDEX MATCH Formula **in** Excel.**

To illustrate, we will use a sample dataset as an example. For instance, the following dataset represents the **Salesman**, **Product**, and **Net Sales **of a company.

**Table of Contents**hide

## Download Practice Workbook

Download the following workbook to practice by yourself.

## Introduction to INDEX Function

**Syntax**

**INDEX(array, row_num,[column_num])**

**Arguments**

**array:** The range from where it’ll pull the data.

**row_num:** The row number of the data to return.

**[column_num]: **The column number of the data to return.

**Objective**

**The INDEX function** retrieves the cell value or reference of the cell located at the intersection of a particular row and column in a given range.

In the following dataset, **Nate Sales** **17000 **is present in the **4th **row and **3rd **column in the range** B5:D10**.

## Introduction to MATCH Function

**Syntax**

**MATCH(lookup_value,lookup_array,[match_type])**

**Arguments**

**lookup_value: **The value to search for in the data range.

**lookup_array**: The data range from where it’ll search for the **lookup_value**.

**[match_type]: **–**1/0/1**. **-1** stands for a value greater than the exact match,** 0** for an exact match, and **1** for the value less than the exact match.

**Objective**

**The MATCH function** returns the relative position of the **lookup_value **in an array.

In the below dataset, the **F4 **cell value is **Frank **(**lookup_value**) and **Frank **is present in the **3rd **position in the **Salesman **section (**B5:B10**). So it returns **3**.

## 9 Examples to Use INDEX MATCH Formula in Excel

Now, we’ll create a formula combining the two functions. We are already aware that the **INDEX **function needs row and column numbers to retrieve data whereas the **MATCH **function returns the location of data. So, we can easily place their argument to get the row and column numbers.

In the following dataset, the **INDEX **function will pull the data from **B5:D10**. The **MATCH **function returns row number **3 **and we’ve specified the column number. So the formula will bring out the data present in the **3rd **row and **3rd **column in the range.

### 1. Two-Way Lookup with INDEX MATCH in Excel

**Two-Way** lookup means fetching both the row number and column number using the **MATCH **function required for the **INDEX **function. Therefore, follow the steps below to perform the task.

**STEPS:**

- First, select cell
**F6**. - Then, type the formula:

`=INDEX(B5:D10,MATCH(F5,B5:B10,0),MATCH(F4,B4:D4,0))`

- Finally, press
**Enter**and it’ll return the value.

🔎 **How Does the Formula Work?**

**MATCH(F5,B5:B10,0)**

The **MATCH **formula returns **3 **to **INDEX **as the row number.

**MATCH(F4,B4:D4,0))**

This **MATCH **formula returns **3 **to **INDEX **as the column number.

**INDEX(B5:D10,MATCH(F5,B5:B10,0),MATCH(F4,B4:D4,0))**

Lastly, the **INDEX **function returns **13500 **which is in the **3rd **row and **3rd **column in the range **B5:D10**.

**Read More: SUMPRODUCT with INDEX and MATCH Functions in Excel**

### 2. INDEX MATCH Formula to Lookup Left

The major advantage of the **INDEX MATCH **formula is that it can retrieve data from the left side of the lookup value. So, learn the steps to carry out the operation.

**STEPS:**

- Firstly, select cell
**F5**. - Next, type the formula:

`=INDEX(B5:B10,MATCH(F4,C5:C10,0)) `

- At last, press
**Enter**and it’ll return the value.

Here, the formula returns the **Salesman’s **name which is on the left side of the lookup value **Cable**.

🔎 **How Does the Formula Work?**

**MATCH(F4,C5:C10,0)**

The **MATCH **formula returns **1 **to **INDEX **as the row number.

**INDEX(B5:B10,MATCH(F4,C5:C10,0))**

Lastly, the **INDEX **function returns **Wilham **which is in the **1st **row in the range **B5:B10**.

**Read More:** **How to Use INDEX MATCH Formula in Excel (9 Examples)**

### 3. Case-Sensitive Lookup Using INDEX MATCH Formula

The **MATCH **function is not case-sensitive by default. However, we can apply** the EXACT function **to lookup that respects upper and lower cases. Hence, follow the process to **Use the INDEX MATCH Formula **to perform **Case**–**Sensitive Lookup **in **Excel**.

**STEPS:**

- In the beginning, select cell
**F5**. - Afterward, type the formula:

`=INDEX(D5:D10,MATCH(TRUE,EXACT(F4,B5:B10),0)) `

- In the end, press
**Enter**to return the value.

🔎 **How Does the Formula Work?**

**EXACT(F4,B5:B10)**

The **EXACT **function returns **TRUE **only for the first data (**B5**) in the range **B5:B10** and **FALSE **for others.

**MATCH(TRUE,EXACT(F4,B5:B10),0)**

This **MATCH **formula returns **1 **to **INDEX **as the row number.

**INDEX(D5:D10,MATCH(TRUE,EXACT(F4,B5:B10),0))**

Lastly, the **INDEX **function returns **2600 **which is in the **1st **row in the range **D5:D10**.

**Read More:** **Examples with INDEX-MATCH Formula in Excel (8 Approaches)**

### 4. Use INDEX MATCH for Closest Match

Sometimes, we may not get the exact match of a lookup value in the lookup array. In that case, we want to search for the closest match. It especially happens with numerical lookup values. Now, learn the process to find the **Closest Match **using the **INDEX MATCH **formula.

**STEPS:**

- Select cell
**F5**at first. - Then, type the formula:

`=INDEX(C5:C10,MATCH(MIN(ABS(D5:D10-F4)),ABS(D5:D10-F4),0)) `

- Lastly, press
**Enter**.

🔎 **How Does the Formula Work?**

**ABS(D5:D10-F4)**

Firstly, the formula subtracts the **F4 **cell value from the range **D5:D10** to generate the differences and we use **the ABS function** to convert the negative results into positive ones.

**MIN(ABS(D5:D10-F4))**

Then, **the MIN function** returns the smallest difference which is** 500**.

**MATCH(MIN(ABS(D5:D10-F4)),ABS(D5:D10-F4),0)**

**MIN(ABS(D5:D10-F4))** formula output is the lookup value (**500**) for the **MATCH **function and the lookup array is **ABS(D5:D10-F4) **formula outputs.

**INDEX(C5:C10,MATCH(MIN(ABS(D5:D10-F4)),ABS(D5:D10-F4),0))**

Eventually, the **INDEX **function returns **Router **as it has the closest **Net Sales **amount to **5000**.

### 5. Multiple Criteria Lookup with INDEX MATCH Formula

One of the most useful operations with the **INDEX MATCH **formula is that it can perform a lookup based on multiple conditions. Follow the steps below to see how we can get the **Net Sales **based on **Salesman **name and **Product**.

**STEPS:**

- First of all, choose cell
**F6**to type the formula:

`=INDEX(D5:D10,MATCH(1,(F4=B5:B10)*(F5=C5:C10),0)) `

- Subsequently, press
**Enter**and you’ll get the result.

🔎 **How Does the Formula Work?**

**MATCH(1,(F4=B5:B10)*(F5=C5:C10),0)**

The **MATCH **formula returns **2 **to **INDEX **as the row number. Here, we compare the multiple criteria by applying boolean logic.

**INDEX(D5:D10,MATCH(1,(F4=B5:B10)*(F5=C5:C10),0))**

Lastly, the **INDEX **function returns **11500 **which is in the **2nd **row in the range **D5:D10**.

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

**Similar Readings**

**Multiple Criteria in Excel Using INDEX, MATCH, and COUNTIF Function****How to use INDEX & MATCH worksheet functions in Excel VBA****Excel Index Match single/multiple criteria with single/multiple results****INDEX MATCH across Multiple Sheets in Excel (With Alternative)****SUMIF with INDEX and MATCH Functions in Excel**

### 6. Excel INDEX MATCH Formula with Wildcard Characters

We can use an asterisk (*****), which is a **Wildcard Character**, to find the partial match for a lookup value. See the below example to perform the task. We have **Nat **in cell **F4**. There is no **Salesman **with that name but we have **Nathan**, which is a partial match.

**STEPS:**

- Firstly, select cell
**F5**. - After that, type the formula:

`=INDEX(D5:D10,MATCH(F4&"*",B5:B10,0))`

- At last, press
**Enter**and it’ll return the**Net Sales**of**Nathan**.

🔎 **How Does the Formula Work?**

**MATCH(F4&”*”,B5:B10,0)**

**F4&”*” **is our lookup value where the asterisk is a wildcard character that represents any number of characters starting with **Nat**. The formula returns **4**.

**INDEX(D5:D10,MATCH(F4&”*”,B5:B10,0))**

Lastly, the **INDEX **function returns **17000 **which is in the **4th **row in the range **D5:D10**.

**Read More:** **INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)**

### 7. Apply INDEX MATCH for Three-Way Lookup in Excel

The advanced use of the **INDEX MATCH **formula is about performing a **Three-Way** lookup. Another syntax of the **INDEX **function is:

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

Where,** [area_num]** (**Optional**) means If the array argument is of multiple ranges, this number will select the specific reference from all the ranges.

In this example, we’ll use this optional argument to return the desired data from any one of the months of **January**, **February**, and **March**. Therefore, follow the below steps to **Use **the **INDEX MATCH Formula **in **Excel **for **Three-Way** **Lookup**.

**STEPS:**

- First of all, choose cell
**F7**to type the formula:

`=INDEX((B6:D7,B11:D12,B16:D17),MATCH(F5,B6:B7,0),MATCH(F6,B5:D5,0),(IF(F4="January",1,IF(F4="February",2,3))))`

- Next, press
**Enter**. Thus, you’ll see the output.

🔎 **How Does the Formula Work?**

**IF(F4=”January”,1,IF(F4=”February”,2,3))**

**The IF function** will return **2 **as our given month is **February**. The **INDEX **function will fetch the value from the **2nd **array i.e. **February**.

**MATCH(F6,B5:D5,0)**

The **MATCH **function returns **3**.

**MATCH(F5,B6:B7,0)**

This **MATCH **function returns **2**.

**INDEX((B6:D7,B11:D12,B16:D17),MATCH(F5,B6:B7,0),MATCH(F6,B5:D5,0),(IF(F4=”January”,1,IF(F4=”February”,2,3))))**

Lastly, the **INDEX **function returns **12500 **which is in the intersection of the **3rd **column and **2nd **row of the **2nd **array.

**Read More:** **How to Use INDEX MATCH Instead of VLOOKUP in Excel (3 Ways)**

### 8. Retrieve Values of Entire Row/Column with INDEX MATCH Formula

Another application of the **INDEX MATCH **formula is retrieving data from the entire row or column. So, learn the procedure to carry out the operation.

**STEPS:**

- In the beginning, select cell
**F5**. Here, type the formula:

`=INDEX(B5:D10,MATCH(F4,B5:B10,0),0)`

- After that, press
**Enter**and it’ll spill the data of the entire**3rd**row in the range**B5:D10**.

🔎 **How Does the Formula Work?**

**MATCH(F4,B5:B10,0)**

The **MATCH **formula returns **3 **to **INDEX **as the row number.

**INDEX(B5:D10,MATCH(F4,B5:B10,0),0)**

The **INDEX **function returns all the values in the **3rd **row in the range **B5:D10**.

**Read More:** **Index Match Sum Multiple Rows in Excel (3 Ways)**

### 9. Find Approximate Match Using INDEX MATCH

The **INDEX MATCH **formula is very useful when finding out the approximate match. In this example, we’ll find the product for an approximate **Net Sales** of **6000**. Hence, follow the process.

**STEPS:**

- Firstly, click cell
**F5**. - Then, type the formula:

`=INDEX(C5:C10,MATCH(F4,D5:D10,1),1)`

- Lastly, press
**Enter**.

**NOTE: **The data should be in **Ascending **or **Descending **order for this formula to work.

🔎 **How Does the Formula Work?**

**MATCH(F4,D5:D10,1)**

The **MATCH **formula uses **1** as the match type argument which will return the largest value that is less than or equal to the lookup value **6000**. Here, it’ll return **2**.

**INDEX(C5:C10,MATCH(F4,D5:D10,1),1)**

The **INDEX **function returns a **Router **that is in the **2nd **row in the range **C5:C10**.

**Read More:** **How to Use INDEX and Match for Partial Match (2 Ways)**

## Why Is INDEX MATCH More Beneficial Than VLOOKUP?

**1. INDEX MATCH Formula Looks Both Left-Right Sides of the Lookup Value**

**The VLOOKUP function** can’t fetch data from the left side of the lookup value. But the **INDEX MATCH **formula can do it.

**2. INDEX MATCH Works with Vertical and Horizontal Ranges**

**VLOOKUP **can only retrieve data from a vertical array, while the **INDEX MATCH **can go through vertical data as well as horizontal ones.

**3. VLOOKUP Fails with Descending Data**

The** VLOOKUP **function can’t handle data of descending order when it comes to the approximate match.

**4. Formula with INDEX MATCH Is Slightly Faster**

**VLOOKUP **is a bit slower function when working with too many rows and columns.

**5. Independent of Actual Column Position**

**VLOOKUP **isn’t independent of the actual column position. So, whenever you delete a column, the **VLOOKUP **function will provide an incorrect result.

**6. VLOOKUP Is Not Difficult to Use**

The **VLOOKUP **function is easier to use compared to the **INDEX MATCH **functions. And most of our lookup operations can be done with **VLOOKUP **easily.

**Read More:** **INDEX MATCH vs VLOOKUP Function (9 Examples)**

## Conclusion

Henceforth, you will be able to **Use **the **INDEX MATCH Formula **in **Excel **with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

How to Use INDEX MATCH Formula in Excel (9 Examples) – Problems encountered

1) Step #7 contains three (3) months your solution contains ONLY two (2) months- What about March

2) Step #8 DOES NOT WORK – when you type a formula into A (ONE) cell the results will NOT populate 2 cells to the right. What is the formula utilized to obtain the data within the 2nd & 3rd cell????????? Does this formula only work in a particular Excel Version? 365?? Also, the only way I could get the function to work for the “Salesman” – Frank response was to utilized Ctrl + Shift + Enter Correct???????????????/ If the user needs to utilize Ctrl + Shift + Enter – Would be great if you ADVISED us of this necessity

Dear

Rich Saunders,Query 1:The method 7 works just fine. If you write March in F4, you will get the values for that month as well.Query 2:The formula is universal for any Excel version. If you write the formula in any cell the formula will get you the entire row value for the name you will write in the F4 cell. For example, if you write “Nathan” in F4 cell and paste the formula in the A1 cell then the formula will write the values of the entire row( B8:D8) in the A1:A3 range.Yes, we should have mentioned the Ctrl+Shift+Enter in the article. We will make sure of that in the next articles. As we used Microsoft 365 so it works by pressing Enter. We are sorry for that.

Regards

ExcelDemy