Excel INDEX MATCH Example (14 Examples + Alternative)

Get FREE Advanced Excel Exercises with Solutions!

For advanced lookups in Excel, the INDEX MATCH formula can be used instead of VLOOKUP. A combination of INDEX and MATCH functions is required for such a purpose. This article will teach you how to perform different lookups using INDEX MATCH. You can see the Excel INDEX MATCH example.

Many times we need to extract values either by looking from the left side or the right side. We can also extract values based on multiple criteria. The INDEX MATCH formula can do all of that. Moreover, it can perform a two-way lookup, three-way look, etc.

overview image of Excel INDEX MATCH example

The above overview image shows 5 different lookup examples through the INDEX MATCH formula. You can see the basic combination to perform the right lookup, the left lookup, the case-sensitive lookup, the two-way lookup, and finding the closest match. Follow along to learn more examples on Excel INDEX MATCH.


Introduction to INDEX Function in Excel

The INDEX function retrieves a value from an array after we input the row and column numbers.

INDEX function syntax

Function Objective:

It returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

Syntax of INDEX Function in Array Form:

=INDEX (array, row_num, [column_num])

Arguments:

argument required/ optional value
array  Required  Pass a range of cells, or an array constant to this argument 
row_num  Required  Pass the row number in the cell range or the array constant 
col_num  Optional  Pass the column number in the cell range or the array constant 

Note:

  • If you use both the row_num and column_num arguments, the INDEX function will return the value from the cell at the intersection of the row_num and column_num.
  • If you set row_num or column_num to 0 (zero), then you will get the whole column values or the whole row values respectively in the form of arrays. You can insert those values into cells using Array Formula.

Syntax of INDEX Function in Reference Form:

=INDEX (reference, row_num, [column_num], [area_num])

Arguments:

argument required/ optional value
reference Required  Pass more than one range or array
row_num  Required  Pass the row number in a specific cell range
col_num  Optional  Pass the column number in a specific cell range
area_num Optional Pass the area number that you want to select from a group of ranges

Note:

  • If pass more than one range or array as the array value, you should pass also the area_num.
  • If the area_num is absent, the INDEX Function will work with the first range. If you pass a value as the area_num, the INDEX function will work in that specific range.
  • If the concepts are not clear, do not worry; go to the next step, where I am going to show you a good number of examples to use Excel’s INDEX function effectively.

overview of INDEX function

The above image shows the product Cherry as the INDEX formula output, which is at the intersection of the 4th row and 2nd column in the range B7:F16.


Introduction to MATCH Function in Excel

The MATCH function returns the relative position of a value in a list or range.

MATCH syntax

Syntax of MATCH Function:

=MATCH(lookup_value,lookup_array,[match_type])

Arguments:

Argument Required/Optional Explanation
lookup_value Required The value to match in the array
lookup_array Required A range of cells or an array reference where to find value
match_type Optional Specifies how Excel matches lookup_value with values in lookup_array. Here, 1 = exact or next smallest, 0 = exact match and -1 = exact or next largest

Return Value:

Returns the lookup value’s relative position.

Available Version:

Workable from Excel 2003.

overview of MATCH function

The image shows the position (4) of Cherry in the product list, C6:C15 as the MATCH formula output.


Excel INDEX MATCH Example: 14 Practical Examples

So far we have seen that the INDEX formula needs row and column numbers to extract a value. Again, the MATCH formula returns a relative position of a value. So what we’ll do here is we’ll input the MATCH formula in the argument of the INDEX formula. Such a combination can perform many different lookups.


1. Basic Example of Excel INDEX MATCH Combination

right lookup

This example performs the right lookup to extract the product based on the Product ID. The formula is:

=INDEX(C5:C14, MATCH(I4, B5:B14, 0))

The MATCH function returns the position, 4, of Product ID 104. The 4 then acts as the row number for the INDEX function argument.

Read More: Excel INDEX MATCH If Cell Contains Text


2. Perform Left Lookup with Excel INDEX MATCH

left lookup

This example shows the left lookup to extract the Product ID from the product. The formula is:

=INDEX(B5:B14, MATCH(I4, C5:C14, 0))

The MATCH function returns the position, 4, of Product Cherry. The 4 then acts as the row number for the INDEX function argument.


3. Use INDEX MATCH for Case-Sensitive Lookup

Case-Sensitive Lookup

Case-sensitive means it will strictly maintain the capital letter and small letters. For case-sensitive lookup, insert the formula:

=INDEX(D5:D14, MATCH(TRUE, EXACT(I4, C5:C14), 0))

The MATCH function returns the position of TRUE from the spilled outputs of the EXACT function.


4. Lookup Two Columns in Excel with INDEX MATCH

Lookup Two Columns

Initially, we’ll find the Product ID from the Product.

=INDEX(B5:B14, MATCH(TRUE, EXACT(C16, C5:C14), 0))

Then, use these 2 lookup values (PineApple, 106) which are in two different columns (Product ID, Product) to get the Sales in Jan. For that purpose, use the formula:

=INDEX(D5:D14, MATCH(C16&C17, C5:C14&B5:B14, 0))

5. Two-Way Lookup Through Excel INDEX MATCH

Two-Way Lookup

Two-way lookup means the 2 lookup values are in a row and a column. Here, Cherry is in the Product column and the Month Mar (March) is in the first row. So, to extract value based on a two-way lookup, apply the formula:

=INDEX(B5:F14, MATCH(I4, C5:C14, 0), MATCH(I5, B4:F4, 0))

Two-way Lookup Using Named Range

naming the range

To avoid using an array in the formula, give a name to the desired range. Here, select B4:F14 and type Table in the Name Box.

Two-way Lookup Using Named Range

Then use the formula:

=INDEX(Table, MATCH(I4, INDEX(Table,, 2), 0), MATCH(I5, INDEX(Table, 1, ), 0))

The array argument in the MATCH function is being replaced by the INDEX function along with the named range Table.


6. Three-Way Lookup Through Excel INDEX MATCH

Three-Way Lookup

Three-way lookup means, one lookup array is a column, another lookup array is a row, and the third lookup array is a dataset. So we’ll search for desired results in columns, rows, and also in different datasets. To perform the three-way lookup, insert the formula:

=INDEX((D6:F10,D14:F18,D22:F26),MATCH(I5,D5:F5,0), MATCH(I6,C6:C10,0),(IF(I4=2023,1,IF(I4=2022,2,3))))

Here, we use the INDEX function in reference form.

Read More: How to Use IF with INDEX & MATCH Functions in Excel


7. Find Closest Match with INDEX MATCH

Finding Closest Match

In this example, we don’t exactly have sales of $300 for any products. But to get the product that has the sales closest to $300, insert the formula:

=INDEX(C5:C14, MATCH(MIN(ABS(D5:D14-G5)), ABS(D5:D14-G5), 0))

Here, ABS(D5:D14-G5) is the absolute value of the difference between the target value of $300 and the range D5:D14. And MIN(ABS(D5:D14-G5)) commands to find the minimum difference of the absolute values.


8. Apply INDEX MATCH with Wildcard Characters

INDEX MATCH with Wildcard Characters

Excel INDEX MATCH example with wildcard characters is necessary if we have the lookup value in partial form. To extract the sales of the product with pine as initials, use the formula:

=INDEX(D5:F14,MATCH(I4&"*",C5:C14,0),MATCH(I5,D4:F4,0))

Read More: INDEX MATCH Multiple Criteria with Wildcard in Excel


9. Lookup Based on Multiple Criteria with INDEX MATCH

Multiple Criteria Lookup

To deal with multiple criteria, use the formula:

=INDEX(D5:F14, MATCH(1,(I4=C5:C14)*(I5=B5:B14),0), MATCH(I6,D4:F4,0))

In this formula, we match not only the product Apple but also the Product ID 110. The MATCH function returns the position where it finds 1 (True).

Read More: Excel INDEX MATCH with Multiple Criteria and Multiple Results


10. Match with Maximum Value Through INDEX, MATCH, and MAX Functions

Matching with Maximum sales

To find the product that has the maximum sales in Jan, use the formula:

=INDEX(C5:C14, MATCH(MAX(D5:D14), D5:D14,0))

Here, the MAX function will find out the maximum sales value of Jan ( $375).

Read More: INDEX, MATCH and MAX with Multiple Criteria in Excel


11. Match with Minimum Value Through INDEX, MATCH, and MIN Functions

Matching with minimum sales

To find the product that has the minimum sales in Jan, use the formula:

=INDEX(C5:C14, MATCH(MIN(D5:D14), D5:D14,0))

Here, the MIN function will find out the minimum sales value of Jan ($225).

Read More: INDEX-MATCH Formula to Find Minimum Value in Excel


12. Match with Closest to Average Value with INDEX, MATCH, and AVERAGE Functions

Matching with Closest to Average sales

To find the product that is closest in sales to the average sales in Jan, use the formula:

=INDEX(C5:C14, MATCH(AVERAGE(D5:D14), D5:D14,-1))

The AVERAGE function finds the average of Jan sales (D5:D14). The output then acts as the lookup value for MATCH.

NOTE:

  • If the lookup column is in ascending order, input 1 in the match type.
  • If the lookup column is in descending order, input -1 in the match type.
  • If the lookup column contains the average value, input 0 in the match type.

13. Deal with Unavailability Using INDEX MATCH with IFNA Function

Dealing with Unavailability Using INDEX MATCH with IFNA Function

If the lookup value is not available in the lookup array, you’ll get the #N/A error. To avoid this, use the formula with the IFNA function:

=IFNA(INDEX(D5:D14, MATCH(I4,C5:C14,0)), "Not Available")

14. Deal with Errors Using INDEX MATCH with IFERROR Function

19-Dealing with Errors Using INDEX MATCH with IFERROR Function

To avoid any kind of errors, use the formula with the IFERROR function:

=IFERROR(INDEX(D5:F14, MATCH(I4, C5:C14, 0)), "Something is wrong")

INDEX MATCH vs. VLOOKUP in Excel

The INDEX MATCH vs. VLOOKUP function preferability depends on the user’s requirements.

1. INDEX MATCH Formula Looks Both Left and 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: How to Use INDEX MATCH Instead of VLOOKUP in Excel


2 Alternatives to Excel INDEX MATCH

Here, we will see two alternatives to the INDEX MATCH function. We used the same dataset.

1. Use VLOOKUP Function

VLOOKUP to perform right lookup

To perform the right lookup, we can use the VLOOKUP formula instead of INDEX MATCH:

=VLOOKUP(I4, B5:F14, 2)

2. Apply XLOOKUP Function

XLOOKUP as INDEX MATCH alternative

The XLOOKUP function is another alternative to INDEX MATCH. To get March Sales of Cherry, insert the formula:

=XLOOKUP(I4, C5:C14, F5:F14)

Read More: XLOOKUP vs INDEX-MATCH in Excel


Things to Remember

  • The total number of rows in the array of the INDEX function argument must be equal to the number of rows in the lookup_array of the MATCH function argument. Or else, you’ll get an error.
  • For using the INDEX MATCH formula for more than one cell, use an absolute cell reference. Then you can just apply AutoFill.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

To sum up, I hope this article on Excel INDEX MATCH example has demonstrated how to use the INDEX MATCH combination to integrate multiple scenarios. You can use the formula for left and right lookups, two-way, and three-way lookups, case-sensitive lookups, and many more. You can also get the closest match, or match with maximum, minimum, and average values. Look over the practice book and put what you’ve learned to use. We are willing to reimburse programs like this because of your support.

If you have any questions, please do not hesitate to contact us. Please let me know what you think in the comments section below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo