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

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.

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:

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

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

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

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

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

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

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

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

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

Related Articles

• IF with INDEX-MATCH in Excel (3 Suitable Approaches)
• How to Select Specific Data in Excel (6 Methods)
• [Fixed!] INDEX MATCH Not Returning Correct Value in Excel (5 Reasons)
• Excel INDEX MATCH to Return Multiple Values in One Cell
• XLOOKUP vs INDEX-MATCH in Excel (All Possible Comparisons)