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 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.
Why isn't my INDEX match formula working? ›
If you believe that the data is present in the spreadsheet, but MATCH is unable to locate it, it may be because: The cell has unexpected characters or hidden spaces. The cell may not be formatted as a correct data type. For example, the cell has numerical values, but it may be formatted as Text.
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 INDEX match better than VLOOKUP? ›
Regarding working with dynamic ranges, INDEX MATCH has a clear advantage over VLOOKUP. With INDEX MATCH, you can easily work with dynamic ranges without worrying about updating the formula every time the range changes. This can be a huge time-saver, making your Excel work much more efficient.
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.
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.
What is better than an 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.
Do INDEX formulas slow down Excel? ›
The INDEX function is fast and is a non-volatile function, which speeds up recalculation. The OFFSET function is also fast; however, it is a volatile function, and it sometimes significantly increases the time taken to process the calculation chain.
Can you INDEX match twice? ›
Two-way lookup with INDEX and MATCH
Use INDEX MATCH & a second MATCH (the INDEX MATCH MATCH formula) in cases where you need to look up a value in a matrix. MATCH is used twice: once for the rows, and. once for the columns.
What is faster, VLOOKUP or INDEX match? ›
In our table, the VLOOKUP formula calculated in 2.3 seconds, INDEX MATCH in 2.6 seconds, OFFSET and MATCH in 2.7 seconds, and XLOOKUP in 3.3 seconds. As you see, the calculation speed increases significantly compared to ranges. Interestingly enough, Excel tables are very fast even with regular references.
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 difference between Xmatch and INDEX match? ›
The 5 key differences between XMATCH and MATCH are: XMATCH defaults to an exact match, while MATCH defaults to an approximate match. XMATCH can find the next larger item or the next smaller item. XMATCH can perform a reverse search (i.e. search from last to first).
Is VLOOKUP obsolete? ›
VLOOKUP is an obsolete function inherited from Lotus-123.
Why is VLOOKUP so difficult? ›
Only searches vertically: VLOOKUP can only search for a value in the leftmost column of a table and retrieve a corresponding value from a column to its right. It cannot search horizontally. Limited to exact or approximate matches: VLOOKUP can only perform exact or approximate matches.
Can I use INDEX match instead of VLOOKUP? ›
Using INDEX and MATCH instead of VLOOKUP
This means that the column containing the value you look up should always be located to the left of the column containing the return value. Now if your spreadsheet isn't built this way, then do not use VLOOKUP. Use the combination of INDEX and MATCH functions instead.
What is the most common mistakes with Excel? ›
Common Excel errors include #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE! To fix these errors, we can adjust the column width, use IFERROR, check for formatting or syntax mistakes, or use formula auditing tools like Evaluate Formula and Error Checking.
What is the reference error in INDEX match match? ›
A #REF! error can occur when a MATCH is found, but the INDEX array is not big enough to include that row. For example, if there are 10 rows in the MATCH lookup_array, but only 5 rows in the INDEX array, a #REF! error will be returned for any MATCH in the 6th to 10th position.
What are the three common formula errors in MS Excel? ›
Some common Excel Errors include: #NULL!, where no intersection exists between cell ranges; #DIV/0!, when a number is divided by zero; #REF!, if there is an invalid cell reference; and #NUM!, when there's a problem with a number in a formula.
Does INDEX match make Excel slow? ›
INDEX MATCH formula
With both tables on the same sheet, INDEX MATCH performed much slower than VLOOKUP (8.9 against 6.6 seconds). But if we move the lookup table to another worksheet, the formula starts working much faster (~ 5 seconds), which is better than VLOOKUP.
