How to combine INDEX, MATCH, and MATCH formulas in Excel as a lookup function
Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
What is INDEX MATCH in Excel?
The INDEX MATCH[1] Formula is the combination of two functions in Excel: INDEX[2] and MATCH[3].
=INDEX() returns the value of a cell in a table based on the column and row number.
=MATCH() returns the position of a cell in a row or column.
Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria. For short, this is referred to as just the Index Match function. To see a video tutorial, check out our free Excel Crash Course.
#1 How to Use the INDEX Formula
Below is a table showing people’s names, height, and weight. We want to use the INDEX formula to look up Kevin’s height… here is an example of how to do it.
Follow these steps:
- Type “=INDEX(” and select the area of the table, then add a comma
- Type the row number for Kevin, which is “4,” and add a comma
- Type the column number for Height, which is “2,” and close the bracket
- The result is “5.8.”
#2 How to Use the MATCH Formula
Sticking with the same example as above, let’suse MATCH to figure out what row Kevin is in.
Follow these steps:
- Type “=MATCH(” and link to the cell containing “Kevin”… the name we want to look up.
- Select all the cells in the Name column (including the “Name” header).
- Type zero “0” for an exact match.
- The result is that Kevin is in row “4.”
Use MATCH again to figure out what column Heightis in.
Follow these steps:
- Type “=MATCH(” and link to the cell containing “Height”… the criteria we want to look up.
- Select all the cells across the top row of the table.
- Type zero “0” for an exact match.
- The result is that Height is in column “2.”
#3 How to Combine INDEX and MATCH
Now we can take the two MATCH formulas and use them to replace the “4” and the “2” in the original INDEX formula. The result is an INDEX MATCH formula.
Follow these steps:
- Cut the MATCH formula for Kevin and replace the “4” with it.
- Cut the MATCH formula for Height and replace the “2” with it.
- The result is Kevin’s Height is “5.8.”
- Congratulations, you now have a dynamic INDEX MATCH formula!
Video Explanation of How to Use Index Match in Excel
Below is a short video tutorial on how to combine the two functions and effectively use Index Match in Excel! Check out more free Excel tutorials on CFI’s YouTube Channel.
Hopefully, this short video made it even clearer how to use the two functions to dramatically improve your lookup capabilities in Excel.
More Excel Lessons
Thank you for reading this step-by-step guide to using INDEX MATCH in Excel. To continue learning and advancing your skills, these additional CFI resources will be helpful:
FAQs
A No Match error usually means that the MATCH portion is unable to find a cell on your source sheet that exactly matches the text you have in that top cell. Based on your formula, it looks like you may have your ranges swapped around.
How do you match multiple answers in Excel? ›
- Step 1: Insert a normal INDEX MATCH formula. INDEX MATCH with multiple criteria is an array formula created from the INDEX and MATCH functions. ...
- Step 2: Change the lookup value to 1. Now, you need to change your normal INDEX MATCH formula into an array formula. ...
- Step 3: Write the criteria.
A common error with Index Match is flipping the syntax of the function. For example, you may switch the values of the lookup and return columns. If you find this happens to you regularly, add a quality assurance step to your process by checking the data you enter to ensure it's correct each time.
What is faster than INDEX match formula? ›
XLOOKUP can perform faster than INDEX MATCH in some cases, especially if you use the binary search mode. However, INDEX MATCH can also be optimized by using Excel tables or dynamic arrays. Formula flexibility. Both formulas are versatile, capable of handling various lookup scenarios.
Why is XLOOKUP better than INDEX MATCH? ›
Let's recap how XLOOKUP outperforms VLOOKUP and INDEX/MATCH: ✅It is the simplest function, with only 3 arguments needed in most cases because the default match_mode is 0 (exact match). ✅It's a single function, unlike INDEX/MATCH, so it's faster to type.
What are the disadvantages of INDEX match? ›
Downsides of INDEX MATCH
- The formula can be unwieldy. Combining two formulas isn't for the faint-of-heart, Excel user. It can take a few tries to get used to what you're doing.
- The dreaded #REF. One common pitfall of using INDEX MATCH arises when the INDEX range is a different size from the MATCH range.
To perform an INDEX MATCH with multiple criteria in Excel, simply use an ampersand (&) to place multiple references in your lookup value and lookup array inputs in the MATCH formula.
Can INDEX return multiple values? ›
You need to use an array formula that combines INDEX, MATCH, SMALL and IF functions to return multiple values horizontally based on a given criteria. And then drag it across horizontally to fill the rest of the cells. This should return the items that match the space code and are approved.
How do I match multiple lookup values in Excel? ›
How to use VLOOKUP for multiple values
- Create a specific helper column on the table's left. ...
- Type your starting formula in the specific cell. ...
- Add the multiple search values. ...
- Input the table array. ...
- Pick a range lookup option.
But if your worksheets contain hundreds or thousands of rows, and consequently hundreds or thousands of formulas, MATCH INDEX will work much faster than VLOOKUP because Excel will have to process only the lookup and return columns rather than the entire table array.
VLOOKUP must be utilized for looking into values from Left to Right. INDEX MATCH can look into the qualities from Left to Right as well as Right to Left. VLOOKUP just can query through vertical lines, for example, segments, and not through columns. INDEX MATCH can query values through lines as well as segments.
Is INDEX match more efficient than VLOOKUP? ›
With sorted data and an approximate match, INDEX-MATCH is about 30% faster than VLOOKUP. With sorted data and a fast technique to find an exact match, INDEX-MATCH is about 13% faster than VLOOKUP.
Is Sumif faster than INDEX match? ›
This is an array formula, to be confirmed by pressing "Ctrl"+"Shift" + "Enter" 3 keystrokes together. Although using Sumifs formula look shorter than Index+Match formula, in fact Index+Match always faster than Sumifs (Lookup function always faster than Math function).
What is the best use of INDEX match? ›
INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria.
What is the INDEX match rule? ›
The INDEX MATCH[1] Formula is the combination of two functions in Excel: INDEX[2] and MATCH[3]. =INDEX() returns the value of a cell in a table based on the column and row number. =MATCH() returns the position of a cell in a row or column.
How do I get my INDEX match to work? ›
How to use INDEX and MATCH in Excel
- Add your data to the sheet. Before using the INDEX and MATCH functions to retrieve specific information, you can first create an Excel file and add your data. ...
- Introduce the INDEX function. ...
- Determine the cell range for your search. ...
- Introduce the MATCH function.
Follow these steps:
- Type “=MATCH(” and link to the cell containing “Kevin”… the name we want to look up.
- Select all the cells in the Name column (including the “Name” header).
- Type zero “0” for an exact match.
- The result is that Kevin is in row “4.”
If you are using INDEX as an array formula along with MATCH in order to be able to retrieve a value, you will need to convert your formula into an array formula, otherwise you will see a #VALUE! error. Solution: INDEX and MATCH should be used as an array formula, which means you need to press CTRL+SHIFT+ENTER.
Why is my INDEX match returning 0? ›
Hence, your INDEX is presented a demand for the 9th row of the range it is given and that contains no data, in what's shown to us, so it returns a "0" as blank cells return "0" in this use.
