The short answer: INDEX and MATCH is a pair of Excel functions that together perform a two-way lookup, letting you find a value at the intersection of a specific row and column. In its simplest form, =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) returns the value in return_range that sits in the same position as the lookup value found in lookup_range.
Why INDEX and MATCH Instead of VLOOKUP?
VLOOKUP has been Excel’s default lookup tool for decades, but it has three well-known limitations that INDEX and MATCH solves:
- Column order: VLOOKUP requires the lookup column to be the leftmost column in the table. INDEX and MATCH can look up from any column, left or right.
- Inserting columns: If you insert a column inside a VLOOKUP table, the column index number shifts and the formula returns the wrong data. INDEX and MATCH references the actual return range, so it stays correct.
- Performance: On very large datasets, MATCH is faster than VLOOKUP because it searches only one column rather than scanning the whole table.
INDEX and MATCH also handles two-way lookups (matching both row and column) more cleanly than VLOOKUP, which requires a nested MATCH for the column argument.
How INDEX and MATCH Work Individually
MATCH returns the relative position of a lookup value in a row or column. For example, if you have a list of product names in A2:A10 and you want to know where “Widget” appears, =MATCH("Widget", A2:A10, 0) returns a number like 3 (meaning it’s the third cell in that range). The third argument, 0, means exact match. Use 1 for approximate match with ascending data, or -1 for approximate match with descending data.
INDEX returns the value at a given position in a range. For example, =INDEX(B2:B10, 3) returns the third value in B2:B10. With two dimensions, =INDEX(A1:C10, 2, 3) returns the value in the second row and third column of A1:C10.
When you nest MATCH inside INDEX, the MATCH supplies the row (or column) number, and INDEX pulls the corresponding value from a different range.
Basic INDEX and MATCH Formula (With Example)
Let’s say you have a table with product names in A2:A10 and prices in B2:B10. You want the price for a product typed in D2.
- In E2, enter:
=INDEX(B2:B10, MATCH(D2, A2:A10, 0)) - Press Enter. The formula returns the price from column B that aligns with the product name in D2.
If D2 contains “Widget” and “Widget” is the third row in A2:A10, MATCH returns 3, and INDEX returns the third value in B2:B10—the price of Widget.
Two-Way Lookup (Row and Column)
Often you need to find a value at the intersection of a specific row label and column header—like a sales figure for a product in a specific month. Use two MATCH functions: one for the row, one for the column.
Assume a table in A1:D10 where A1 is a header, B1:D1 contain month names (Jan, Feb, Mar), and A2:A10 contain product names. To get the sales for “Widget” in “Feb”:
- Enter the product name in F2 and the month in G2.
- In H2, enter:
=INDEX(B2:D10, MATCH(F2, A2:A10, 0), MATCH(G2, B1:D1, 0))
MATCH(F2, A2:A10, 0) returns the row number, MATCH(G2, B1:D1, 0) returns the column number, and INDEX returns the value at that row/column intersection.
Common Mistakes and How to Avoid Them
- Mismatched range sizes: The lookup range and return range must be the same size (same number of rows or columns). If not, MATCH may return a position that INDEX can’t use correctly.
- Exact vs. approximate match: For most lookups, use
0for exact match. Using1without sorting ascending can give wrong results because approximate match assumes the data is sorted. - Spelling and extra spaces: MATCH with exact match is case-insensitive but not space-insensitive. “Widget ” (with trailing space) won’t match “Widget”. Use TRIM or clean data.
- #N/A errors: If MATCH can’t find the value, it returns #N/A. Wrap the formula in IFERROR to show a friendly message, e.g.,
=IFERROR(INDEX(B2:B10, MATCH(D2, A2:A10, 0)), "Not found").
INDEX and MATCH vs. XLOOKUP
XLOOKUP, introduced in Excel 2021 and Microsoft 365, simplifies lookups with a single function: =XLOOKUP(lookup_value, lookup_array, return_array). It handles left lookups, two-way lookups (via nested XLOOKUP), and returns #N/A if not found. Many users now prefer XLOOKUP because it’s easier to write and read.
However, INDEX and MATCH remains relevant for compatibility with older Excel versions (2016 and earlier) and for certain advanced scenarios like returning the last non-empty cell or working with array formulas. XLOOKUP is not available in Excel 2019 or older.
Frequently Asked Questions
Can I use INDEX and MATCH to look up from right to left? Yes—that’s a primary advantage. With VLOOKUP, the lookup column must be the leftmost. With INDEX and MATCH, the lookup range can be any column, and the return range can be to its left or right.
How do I handle multiple criteria with INDEX and MATCH? Use array formulas or helper columns. For example, to match both Product and Month, you can use =INDEX(C2:C10, MATCH(1, (A2:A10=F2)*(B2:B10=G2), 0)) and press Ctrl+Shift+Enter (in older Excel) or just Enter in Excel 365. This creates an array of 0s and 1s, and MATCH finds the first 1.
Why is my INDEX MATCH returning #N/A even though the value exists? Check for extra spaces, non-printing characters, or number stored as text. Also verify that the lookup range and return range are the same size. Use TRIM and CLEAN to clean data.
Is INDEX MATCH faster than VLOOKUP? In most cases, yes, especially on large datasets, because MATCH only searches one column. But XLOOKUP is also fast and often simpler. The speed difference is rarely noticeable on small tables.
Can I use INDEX MATCH with wildcards? Yes, but only with approximate match (0 is not allowed with wildcards). Use =INDEX(B2:B10, MATCH("*"&D2&"*", A2:A10, 0)) to find a partial match. Note that this returns the first match and is case-insensitive.
Does INDEX MATCH work in Google Sheets? Yes, both functions exist and work the same way in Google Sheets. The syntax is identical, though XLOOKUP is also available in newer Google Sheets versions.
Related
- Excel Conditional Formatting Color Scales Explained
- Excel Choose Date from Calendar Cell: How to Add a Date Picker
- Excel Center Louisville KY: What It Is & How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Box Plot Formula: Quartiles, IQR, and WhiskersA box plot has no single formula: it is built from a five-number summary—minimum, first quartile (Q1), median,…
- Split CSV in Excel: Choose the Right MethodIf you mean splitting CSV data in Excel, use Data > From Text/CSV to import it and choose…
- Excel Data Analysis Books: How to Choose the Right OneGood Excel data-analysis books include Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston for business…
- Filter Cells With Specific Text in Excel: A Quick GuideTo filter cells by text in Excel for Microsoft 365 on Windows, select your data, choose Data >…
- What-If Analysis in Excel: Definition and ExamplesIn Excel, what-if analysis means changing input values in a spreadsheet model to see how those changes affect…
- Excel TODAY Formula: Show Today’s Date AutomaticallyTo show today’s date in an Excel cell and have it update, enter =TODAY(). The function returns the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.