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.
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.
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.
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.
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.
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
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
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 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
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 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
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.
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 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
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
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
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
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
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
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.
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
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
To perform the right lookup, we can use the VLOOKUP formula instead of INDEX MATCH:
=VLOOKUP(I4, B5:F14, 2)2. Apply XLOOKUP Function
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.
