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