Excel INDEX MATCH: Practical Guide for Better Lookups (vs. VLOOKUP)
Excel’s INDEX MATCH combination is one of the most powerful Excel formulas available. It provides significantly more flexible and reliable lookups than the VLOOKUP function, making it a preferred choice among intermediate and advanced Excel users.
In financial modeling and financial dashboards, INDEX MATCH stands as a key Excel function. It excels at retrieving information (such as product names based on product IDs) from datasets. Its backward compatibility in Microsoft Excel, integration with other spreadsheet applications like Google Sheets, and a more consistent syntax with Excel's SUMIFS (another essential function) ensure it remains a strong competitor against the newer XLOOKUP function.
Learn how to use INDEX MATCH for simple lookups, two-way lookups, and multi-criteria lookups.
Table of Contents
- 1. What is INDEX MATCH (and Why Use It Over VLOOKUP?)
- 2. How to Use INDEX and MATCH in Excel (Basic Example)
- 3. Two‑Way Lookup with INDEX MATCH (Matching Rows and Columns)
- 4. Multiple Criteria Lookup with INDEX MATCH
- 5. How Does INDEX MATCH Compare to XLOOKUP?
- 6. Accelerate Excel and INDEX MATCH
- 7. Conclusion
- 8. FAQ: Common Questions about INDEX MATCH in Excel
What is INDEX MATCH (and Why Use It Over VLOOKUP)?
INDEX MATCH refers to using Excel’s INDEX and MATCH functions together to perform lookups.
In simple terms, INDEX(range, position) returns the value at a certain position in a range of cells, and MATCH(value, range, 0) finds the relative position of a lookup value in a range.
When combined, MATCH locates the row or column of the value you need, and INDEX retrieves the result from that position. This approach can do anything VLOOKUP (and HLOOKUP) can do, and more.
Why INDEX/MATCH is better than VLOOKUP:
Lookup in any direction (including left): VLOOKUP can only search the leftmost column and return data to the right. INDEX/MATCH can look left, right, up, or down because you independently choose both the lookup range and the return range.
Resilient to column changes: VLOOKUP can break if a column is inserted or deleted, because it uses a fixed index number. INDEX/MATCH does not break when columns move, because you specify the exact ranges.
No 255-character limit: VLOOKUP fails with very long lookup values. INDEX/MATCH does not have this limitation.
Faster on large data: INDEX/MATCH can be faster than VLOOKUP in big datasets. It only looks at the necessary columns, which reduces processing overhead.
INDEX/MATCH is also very flexible. It works for horizontal lookups, two-way (matrix) lookups, case-sensitive lookups, and even multiple-criteria searches.
Let us see how to use it step by step.
How to Use INDEX and MATCH in Excel (Basic Example)
To illustrate the basics, let us look at a simple scenario. Suppose you have a list of products and their prices in two adjacent columns. You want to look up the price of a specific product.
=INDEX($B$2:$B$5, MATCH("Cherry", $A$2:$A$5, 0))
Identify the lookup value and ranges. For instance, the product name is the lookup value, the products column is the lookup range, and the prices column is the return range.
Write the MATCH function to find the position of the lookup value.
Write the INDEX function to retrieve the price.
You will use the result of MATCH as the row number: This returns the price of "Cherry" from the correct row in the price range.
In short, MATCH finds the row number for the product, and INDEX returns the corresponding value from the price column. This is similar to a VLOOKUP but with more flexibility.
Tip: Always use 0 (or FALSE) for exact matches, unless you intend a range lookup with sorted data. If you omit it, MATCH may return an incorrect nearest match.
Two-Way Lookup with INDEX MATCH (Matching Rows and Columns)
A two-way lookup (or matrix lookup) matches two criteria: one along rows and another along columns.
For example, imagine a table of population data by country (rows) and year (columns). You want the population of a specific country in a specific year.
Match the row: Use MATCH("China", A2:A11, 0) to find the row number of "China" in the country list.
Match the column: Use MATCH("2015", B1:D1, 0) to find the column number of 2015 in the header row.
Combine in INDEX:
=INDEX($B$2:$D$11, MATCH("China", $A$2:$A$11, 0), MATCH("2015", $B$1:$D$1, 0))
This returns the value at the intersecting row and column for "China" and "2015." This method is often called INDEX MATCH MATCH because you use MATCH twice.
This two-dimensional lookup is very useful for retrieving values when both the row and column need to be matched. It is also used for things like matching a product and a month, or a student and a subject.
Note: In older Excel versions without dynamic arrays, you might need to press Ctrl+Shift+Enter for array formulas if you use multiple criteria. Excel 365 handles this automatically with dynamic arrays.
Multiple Criteria Lookup with INDEX MATCH
You can also search based on more than one condition. For example, you might need to match both a product name and a category.
Concatenation method: One simple approach is to create a helper column that combines both fields into one cell (for example,
A2 & "-" & B2) and then match on that combined value.Array formula method: A more elegant (though initially intimidating) method uses an array formula with MATCH. The idea is to use multiplication to represent the logical AND of criteria:
=INDEX(C2:C10, MATCH(1, (A2:A10=F1)*(B2:B10=F2), 0))
This formula checks which row meets both conditions (A2:A10=F1) AND (B2:B10=F2). It then returns the correct value from C2:C10. This method is very flexible but can be a bit complex if you are new to array formulas.
The formula above can be extended to more criteria by multiplying additional (Range=Value) parts inside MATCH. Just remember that all ranges used in the multiplication must be the same size, and the INDEX return range must align in size as well.
Note: Older versions of Excel may require pressing Ctrl+Shift+Enter for array formulas.
How Does INDEX MATCH Compare to XLOOKUP?
Excel’s newer XLOOKUP function (available in Excel 365 and Excel 2021+) is a modern replacement for both VLOOKUP and INDEX/MATCH. It was introduced to simplify lookups and overcome the limitations of earlier functions. Here is how they compare:
Functionality: XLOOKUP can do everything INDEX/MATCH does, and more. It looks up a value in a row or column and returns a corresponding value from another row/column without needing two functions. It natively supports lookups to the left, approximate matches, wildcards, and even searching from bottom or top. It also has a built-in not found message if the value is not found (avoiding the need for IFERROR).
In other words, XLOOKUP provides the flexibility of INDEX/MATCH with a simpler syntax.
Ease of use: For many users, XLOOKUP is generally easier to write and read than an INDEX/MATCH formula.
Performance: In terms of calculation speed, XLOOKUP and INDEX/MATCH are comparable and differences are not meaningful in practice.
Compatibility: INDEX and MATCH have been around forever, so they work in all modern Excel versions. XLOOKUP is newer; if you share a workbook with someone using Excel 2016 or 2019 (who does not have XLOOKUP), they will see errors. Plus, if your file will be shared with Google Sheets users, you will need INDEX/MATCH as XLOOKUP is not part of Google Sheets.
First reference: Double clicking a cell (or using the CTRL + ] shortcut) lets you jump to the first range reference in a formula. In an XLOOKUP the first reference is the lookup value, whereas in the INDEX/MATCH formula, it is the value range. For formula auditing purposes, jumping straight to the lookup range is more convenient and intuitive.
(I personally prefer INDEX/MATCH over XLOOKUP, not just for backwards compatibility but also because it feels more intuitive to me. The two most important functions I’ve used professionally are SUMIFS (for aggregation) and INDEX/MATCH (for lookups). In both cases, the key range, whether it's the sum range or the return range, comes first in the formula, which makes them feel consistent.)
Accelerate Excel and INDEX MATCH
Writing complex INDEX/MATCH formulas can be tedious for newer (and advanced!) users. A Microsoft Excel productivity add-in, such as Accelerate Excel, can help:
Guided formula creation: It can prompt you for the necessary ranges and cells, step by step. You select the return range, the lookup range, the lookup value, and so forth. The tool then inserts a final INDEX/MATCH formula.
Conversion to direct cell links: If you have many INDEX/MATCH formulas, the add-in can convert each formula into a direct cell reference to the matched cell. This can reduce complexity and improve calculation speed once you have confirmed the results.
Explore Formula: A formula navigation pane that helps you find and select cell references within formulas. Ideal for verifying complex formulas like INDEX MATCH as well as tracing formula precedents in general.
Guided INDEX MATCH Formula Creation
Excel users who do not write INDEX / MATCH formulas often may struggle with the syntax and need to look up how it works. The guided inputs help because they tell the user what to select.
Even advanced users gain an advantage thanks to the convenience factor. Why?
After you have selected the INDEX range (return range), you can be lazy and adjust only part of the MATCH ranges (lookup). The tool auto‑adjusts all ranges, and all ranges stay the same size.
Usually you want to look up a value that is on another sheet. When you write INDEX / MATCH manually, you start on the sheet where you will place the formula. Then you move to the sheet with the return range. Next you return to the first sheet for the criterion (the criterion is almost always on the same sheet as the formula). Finally you move again to the other sheet for the lookup range. The tool shortens the process. You select return range, then lookup range, then criterion. You jump between sheets fewer times.
Note: Jumping between sheets in a manual INDEX / MATCH often creates a “same‑worksheet reference”. The criterion reference includes the sheet name even when the formula sits on that sheet. This is not best practice. Such references can lock cell addresses during a sort and prevent you from sorting your lookups. The add-in avoids this problem, reduces typing errors, and contributes to data integrity.
Convert INDEX / MATCH to Direct Cell Reference
With Accelerate Excel you select cells that contain INDEX / MATCH formulas and convert them to direct references (simple cell links).
Why do this?
Reduce complexity: The dynamic power of INDEX and MATCH is great for advanced users. However, when clients or colleagues with less technical Excel skill need to review the workbook, a direct link lets them follow the data flow easily, reduces the risk of mistakes, and helps them verify numbers quickly.
Audit purposes: If a lookup result appears unusual, you can view the source cell immediately. Direct links show the exact location of the data, so you confirm the value quickly.
By switching to “hard” cell links when extra flexibility is no longer required, you keep your model transparent, efficient, and easy for every colleague to follow.
(When I convert INDEX/MATCH formulas to direct references, I usually leave one INDEX/MATCH intact and hide it, so I can quickly restore the lookups if needed without having to rebuild the formula.)
Conclusion
INDEX MATCH is a cornerstone of advanced Excel data analysis. It handles data lookups more flexibly than VLOOKUP. It allows two-way lookups, multi-criteria matches, and is more resistant to broken formulas when columns move.
If you have newer Excel software, XLOOKUP is also an excellent choice, and it can simplify your Excel formulas. However, INDEX MATCH remains important because it works across many Excel versions and helps you understand the mechanics behind lookup functions.
FAQ: Common Questions About INDEX MATCH in Excel
Why is my INDEX MATCH formula not working?
If your INDEX/MATCH is not returning the correct result (or is giving an error like #N/A), consider these troubleshooting tips:
Exact match issues: Ensure you used
0(FALSE) as the match type in the MATCH function. If you omit it, MATCH might be doing an approximate search, leading to wrong results or #N/A. Always specify the third argument as 0 for exact matches unless your data is sorted and you intentionally want a range lookup.Typos or mismatched data: The lookup value must exactly match the data in the lookup range. Typos, extra spaces, or differences in formatting (e.g. one is a number stored as text, and the other is a number) will cause the MATCH to fail. For example, "Apple " (with a trailing space) will not match "Apple". Check that both the lookup value and the values in your table have no extra spaces or inconsistencies.
Wrong ranges or cell references: Double-check that the range used in INDEX corresponds to the range used in MATCH. They should cover parallel arrays. If your INDEX is pulling from B2:B10 but your MATCH is searching A2:A9 (one row short), you’ll get an incorrect result or error. Also ensure your cell references are fixed correctly with
$if you are copying the formula to other cells. A common mistake is not locking the ranges, which causes them to shift when filling the formula down or across, leading to mismatches.Duplicate entries: MATCH will return only the first occurrence of the lookup value. If your data has duplicates and you expected a later one, INDEX/MATCH might appear to “not work” when in fact it’s just finding the first match. Remove duplicates or use a more advanced approach if you need a different occurrence.
Array formula requirements: If you’re using an INDEX/MATCH combination with multiple criteria (e.g., using an array expression like
MATCH(1, (Range1=Value1)*(Range2=Value2), 0)), remember that in Excel versions prior to Office 365 you must press Ctrl+Shift+Enter to enter it as an array formula. Forgetting this will make the formula show an error or an incorrect result because it hasn’t been evaluated as an array. (In Office 365 with dynamic arrays, this is handled automatically.)
By methodically checking each of the above, you can usually pinpoint why an INDEX/MATCH formula isn’t working. It often comes down to a simple range reference issue or a minor data mismatch.
Can INDEX MATCH return multiple values?
By default, INDEX/MATCH returns only the first matching value that it finds. It’s fundamentally designed to return a single result (the value at the first position where the match is found). If you have a dataset with multiple entries that meet your criteria, a basic INDEX/MATCH will not retrieve all of them – it will only grab the first one.
However, there are ways to get multiple matches, but they require more complex formulas or newer functions:
Using new functions (Excel 365): If you have Excel 365 or later, you can use the
FILTERfunction to return all rows that match a criteria. For example,=FILTER(B2:B10, A2:A10="Apple")would spill an array of all values in B2:B10 where the corresponding cell in A2:A10 is "Apple". This can effectively return multiple results without a complicated formula. (You could also use the newXLOOKUPwith the search mode set to find all occurrences, but FILTER is simplest for retrieving all matches.)Using older approaches: In legacy Excel, it’s possible to retrieve multiple matches using an array formula with functions like
SMALLorINDEXin combination withIF. These formulas can get pretty complicated. One pattern uses a helper column or an array calculation to sequentially find 1st match, 2nd match, etc., often involving the ROW function and SMALL to pick the nth match. Another approach is usingTEXTJOINin Office 365 with IF to concatenate multiple results. These methods work but are beyond the scope of a basic INDEX/MATCH tutorial.Other tools: Some Excel add-ins (and even Excel’s Power Query or PivotTables) can be used to gather all matching entries more easily. For instance, a PivotTable could list all records matching a certain criteria, or Power Query could filter and display results without writing complex formulas.
In summary, a single INDEX/MATCH will give you a single value. To get multiple values, you’ll need to either use a more advanced formula or take advantage of Excel’s newer functions or tools.
Is INDEX MATCH faster than VLOOKUP?
For exact match lookups on large datasets, INDEX/MATCH generally has a performance edge over the VLOOKUP function. The reason is that VLOOKUP scans an entire table array and then plucks a result, whereas INDEX/MATCH searches only the lookup column and then directly retrieves from the result column. In practice, this means if you have thousands of rows and many lookup formulas, INDEX/MATCH can recalc a bit faster because it’s handling smaller chunks of data.
That said, for moderate-sized data (a few hundred rows), you likely won’t notice a performance difference – both methods calculate almost instantaneously. Also, if you were to use VLOOKUP in its approximate match mode on sorted data, it can be extremely fast (binary search), but approximate matches are not commonly used for typical lookup tasks.
In short, yes, INDEX/MATCH is often a bit faster than VLOOKUP for exact lookups, especially as data grows. But the difference might only become apparent in very large spreadsheets. Many users choose INDEX/MATCH not just for speed but for the flexibility and robustness reasons discussed earlier. If performance is a concern (e.g., you have tens of thousands of formulas), INDEX/MATCH is a smart choice.