VLOOKUP can only look right. It also breaks whenever columns are inserted or deleted. The INDEX MATCH combination solves both problems — it looks in any direction and never depends on column position numbers. INDEX retrieves a value by row and column number. MATCH finds the position of a value in a list. Together, they form Excel's most flexible lookup pattern.
This guide covers 15 practical INDEX MATCH patterns, organised into three tiers. Consequently, you can work through them in order or jump to whichever pattern your task requires.
Availability: INDEX and MATCH work in every Excel version from Excel 2003 onwards, including Microsoft 365, Excel 2024, 2021, 2019, and 2016. Multi-criteria array formulas (Examples 6, 7, 11, 13) require Ctrl+Shift+Enter in Excel 2019 and earlier. In Excel 365 they enter normally.
How Do INDEX and MATCH Work?
MATCH finds the row number. INDEX uses that number to retrieve the value. Understanding each function independently first makes the combined formula much easier to read and debug.
| Function | Syntax | What it does |
|---|
| INDEX | INDEX(array, row_num, [col_num]) | Returns the value at a given row and column position within a range. Omit col_num to return the entire row. Set row_num to 0 to return the entire column. |
| MATCH | MATCH(lookup_value, lookup_array, [match_type]) | Returns the position of a value in a list. match_type 0 = exact match (use this for unsorted data). match_type 1 = largest value less than or equal to lookup (requires ascending sort). match_type -1 = smallest value greater than or equal to lookup (requires descending sort). |
Always write match_type 0 explicitly: MATCH defaults to 1 (approximate match), which requires sorted data. On unsorted data it returns incorrect results. Forgetting to supply 0 is the single most common cause of wrong INDEX MATCH results. Write it every time.
Tier 1: Foundations (Examples 1–5)
● Foundations — master these before moving on
1
Basic lookup — find a value in a table
This is the standard pattern. MATCH locates which row holds the lookup value. INDEX then retrieves the value from that row in a different column. The combination replaces VLOOKUP and does not break when columns shift.
A — Name
B — Dept
C — Salary
Find James's salary. MATCH locates "James" in column A (returns 2).
INDEX retrieves row 2 from column C.
=INDEX(C2:C100, MATCH("James", A2:A100, 0))
→ £47,000
Use a cell reference for the name — better for dashboards (F1 holds "James"):
=INDEX(C2:C100, MATCH(F1, A2:A100, 0))
Wrap in IFERROR for graceful "not found" handling:
=IFERROR(=INDEX(C2:C100, MATCH(F1, A2:A100, 0)), "Not found")
2
Left lookup — return a value to the left of the search column
VLOOKUP cannot look left — the return column must sit to the right. INDEX MATCH has no such restriction. The return range and the lookup range are independent, so either can be in any column.
Data: A=OrderID, B=Region, C=Sales.
Return the OrderID (column A) for a given region (column B).
Column A is to the LEFT of column B — VLOOKUP cannot do this.
=INDEX(A2:A100, MATCH("East", B2:B100, 0))
→ Returns the OrderID for the first East-region row
Return and lookup ranges can also be non-adjacent:
INDEX returns from column E; MATCH searches column C.
=INDEX(E2:E100, MATCH(F1, C2:C100, 0))
✓
Column insertions and deletions never break INDEX MATCH because it references ranges by name, not by position number. This is the main reason experienced users prefer it over VLOOKUP.
3
Two-way lookup — match both a row header and a column header
Two MATCH functions inside one INDEX formula find the intersection of a row and a column. The first MATCH finds the row number. The second MATCH finds the column number. This pattern is often called INDEX MATCH MATCH.
Row headers in A2:A10, column headers in B1:D1, data in B2:D10.
Find East + Feb: first MATCH → row, second MATCH → column.
=INDEX(B2:D10,
MATCH("East", A2:A10, 0),
MATCH("Feb", B1:D1, 0))
→ 1,550
Dynamic version — both criteria come from cells G1 and G2:
=INDEX(B2:D10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:D1, 0))
More Foundation Patterns — Horizontal and Dynamic Lookups
4
Horizontal lookup — MATCH across a row (replaces HLOOKUP)
MATCH works on horizontal ranges just as well as vertical ones. Pass a row range to MATCH and it returns the column position. INDEX then retrieves from a data row at that column — replacing HLOOKUP without the column-position fragility.
Month headers across row 1 (B1:M1), values across row 2 (B2:M2).
Find the value for March. MATCH locates "March" in row 1.
=INDEX(B2:M2, MATCH("March", B1:M1, 0))
HLOOKUP equivalent for comparison:
=HLOOKUP("March", B1:M2, 2, FALSE) breaks if a column is inserted before March.
INDEX MATCH finds "March" by name — column order is irrelevant.
5
Dynamic column selection — pick the return column from a dropdown
Nesting MATCH in the column argument of INDEX makes the return column itself dynamic. A user selects a field name from a dropdown, and the formula fetches that field for the chosen row automatically — no formula editing required.
Employee table: A=Name, B=Dept, C=Salary, D=Start Date. Headers in row 1.
G1 = chosen employee, G2 = chosen field ("Salary", "Dept", "Start Date").
The outer MATCH finds the row; the inner MATCH finds the column.
=INDEX(A2:D100,
MATCH(G1, A2:A100, 0),
MATCH(G2, A1:D1, 0))
G1="James", G2="Salary" → James's salary
G1="James", G2="Dept" → James's department
Just change the dropdowns — no formula editing needed.
Add Data Validation dropdowns: Set G1's validation to a list of names from column A. Set G2's validation to a list of column headers from row 1. The formula then updates automatically as soon as a dropdown changes.
Tier 2: Intermediate (Examples 6–10)
●● Intermediate — build on the foundations
6
Multiple criteria — concatenation method
Standard MATCH matches one column at a time. The simplest multi-criteria technique joins the criteria values and the lookup ranges with ampersand. Concatenation avoids the need for a helper column while keeping the formula readable.
Find the price where Product = "Widget" AND Size = "Large".
Join both lookup values together; join both lookup columns together.
MATCH then finds the row where the combined string appears.
=INDEX(C2:C100,
MATCH(G1&G2,
A2:A100&B2:B100, 0))
← Ctrl+Shift+Enter in Excel 2019 and earlier
Add a delimiter to prevent false composite matches.
"Widget" + "Large" vs "WidgetLarge" would both match without a separator.
Using "|" ensures only true combinations match.
=INDEX(C2:C100,
MATCH(G1&"|"&G2,
A2:A100&"|"&B2:B100, 0))
7
Multiple criteria — Boolean array method
Multiplying condition arrays together applies AND logic. Each condition produces TRUE/FALSE for every row. Multiplying them means only rows that satisfy all conditions produce a 1. MATCH then finds the first row with a 1. This approach handles three or more criteria cleanly and is easier to extend than concatenation.
Find the price where Region = "East" AND Product = "Widget" AND Year = 2026.
Each condition produces an array of TRUE/FALSE.
Multiplication converts TRUE to 1 and FALSE to 0, then applies AND logic.
MATCH finds the first row where all three conditions are TRUE (product = 1).
=INDEX(D2:D100,
MATCH(1,
(A2:A100="East") *
(B2:B100="Widget") *
(C2:C100=2026),
0))
← Ctrl+Shift+Enter in Excel 2019 and earlier
Non-array version — wrap conditions in INDEX to force array evaluation:
The nested INDEX acts as an array container. No Ctrl+Shift+Enter needed.
=INDEX(D2:D100,
MATCH(1,
=INDEX((A2:A100="East")*(B2:B100="Widget"), 0, 1),
0))
8
Wildcard lookup — partial text matching with * and ?
MATCH supports wildcards. An asterisk matches any number of characters. A question mark matches exactly one character. These patterns let you match partial product names, name prefixes, or any text that contains a known substring.
Find the salary of the first person whose name starts with "Jam":
"Jam*" matches "James", "Jamie", "Jameson", etc.
=INDEX(C2:C100, MATCH("Jam*", A2:A100, 0))
Cell reference with wildcard appended — F1 holds "Jam":
=INDEX(C2:C100, MATCH(F1&"*", A2:A100, 0))
Contains match — find any row where column A contains "Widget" anywhere:
=INDEX(C2:C100, MATCH("*Widget*", A2:A100, 0))
Single-character match — "?" replaces exactly one character:
"Ja?" matches "Jam", "Jan", "Jay" — any 3-character string starting with "Ja".
=INDEX(C2:C100, MATCH("Ja?", A2:A100, 0))
Cross-Sheet and Approximate Match Lookups
9
Cross-sheet lookup — search a range on a different worksheet
INDEX MATCH references any sheet using the standard SheetName!Range notation. Both the return range and the lookup range can point to different sheets. As a result, you can pull data from a source sheet without consolidating it first.
Look up an employee's salary from the "Staff" sheet.
The formula lives on the summary sheet; the data is on Staff.
=INDEX(Staff!C:C, MATCH(A2, Staff!A:A, 0))
Dynamic sheet reference — sheet name stored in cell H1:
INDIRECT builds the reference string at runtime from a cell value.
Note: INDIRECT is volatile and recalculates on every change.
=INDEX(INDIRECT(H1&"!C:C"),
MATCH(A2, INDIRECT(H1&"!A:A"), 0))
Cross-workbook reference — external workbook must be open for live results:
=INDEX([Budget.xlsx]Q1!C:C,
MATCH(A2, [Budget.xlsx]Q1!A:A, 0))
10
Approximate match — find the right commission tier or tax bracket
Setting match_type to 1 finds the largest value less than or equal to the lookup value. This is perfect for tax brackets, commission tiers, and discount thresholds — anywhere a value falls into a band. The lookup column must be sorted ascending for match_type 1 to work.
Commission tier table sorted ascending (A=Threshold, B=Rate):
0 → 5%, 10000 → 8%, 25000 → 12%, 50000 → 15%
For a sale of £32,000, MATCH finds 25,000 (largest value ≤ 32,000).
INDEX retrieves the corresponding rate: 12%.
=INDEX(B2:B5, MATCH(G1, A2:A5, 1))
→ 12% (for a sale of £32,000)
match_type 1 requires A2:A5 sorted ascending.
Descending approximate match — match_type -1:
Finds the smallest value ≥ lookup. Requires descending sort.
Useful for grade boundaries where you want the lowest passing threshold.
=INDEX(B2:B5, MATCH(G1, A2:A5, -1))
Tier 3: Advanced (Examples 11–15)
●●● Advanced — powerful array and combination techniques
11
Case-sensitive lookup — distinguish uppercase from lowercase
MATCH is not case-sensitive. It treats "APPLE" and "apple" as identical. To distinguish case, use EXACT inside an array formula. EXACT compares strings character by character and returns TRUE only when case matches precisely.
Data has both "LONDON" and "London" as region values.
Standard MATCH finds the first occurrence regardless of case.
EXACT forces precise case comparison — only "LONDON" (uppercase) matches.
=INDEX(C2:C100,
MATCH(TRUE,
EXACT("LONDON", A2:A100),
0))
← Ctrl+Shift+Enter in Excel 2019 and earlier
Use a cell reference for the lookup value:
G1 holds the exact case string you want to match.
=INDEX(C2:C100,
MATCH(TRUE, EXACT(G1, A2:A100), 0))
12
Find the last matching value — reverse search
Standard MATCH returns the first match. When data has duplicates, you often need the last one — for example, the most recent transaction for a customer, or the latest price for a product. Two approaches handle this efficiently.
Find the LAST occurrence of "James" in column A and return the value from C.
Approach 1 — LOOKUP trick (no array entry needed, works in all versions):
1/(A2:A100="James") produces 1 for matching rows and an error for others.
LOOKUP(2, ...) finds the last 1 in the array (2 is larger than any valid value).
=LOOKUP(2, 1/(A2:A100="James"), C2:C100)
→ Value in C for the LAST "James" row. No Ctrl+Shift+Enter needed.
Approach 2 — XMATCH with reverse search (Excel 365 only):
search_mode -1 searches from last to first — finds the last occurrence first.
=INDEX(C2:C100,
XMATCH("James", A2:A100, 0, -1))
13
Closest match — find the nearest numeric value
When no exact match exists, you sometimes need the nearest value — the date closest to today, or the product weight nearest to a target. MIN combined with ABS creates the closest-match logic inside MATCH.
Find the item (column A) whose weight (column B) is nearest to target in G1.
ABS(B2:B100 - G1) computes absolute difference for every row.
MIN finds the smallest difference.
MATCH locates that minimum in the difference array.
INDEX retrieves the corresponding item name.
=INDEX(A2:A100,
MATCH(
MIN(ABS(B2:B100-G1)),
ABS(B2:B100-G1),
0))
← Ctrl+Shift+Enter in Excel 2019 and earlier
Closest date to today — same pattern, different data type:
Dates are stored as numbers, so ABS subtraction works identically.
=INDEX(A2:A100,
MATCH(
MIN(ABS(B2:B100-TODAY())),
ABS(B2:B100-TODAY()),
0))
Using INDEX and MATCH Independently
14
Return an entire row or column — INDEX without MATCH
INDEX does not always need MATCH. Setting row_num to 0 returns the entire column. Setting col_num to 0 returns the entire row. This trick feeds dynamically selected columns into aggregate functions like SUMIF, AVERAGE, or MAX — without hardcoding column references.
Return the entire 3rd column of table B2:E100.
Setting row_num to 0 returns all rows in that column.
=INDEX(B2:E100, 0, 3)
→ Returns the entire column D of the data block
Feed a dynamically selected column into SUMIF.
G1 holds the column number the user wants to sum.
SUMIF then sums whichever column the user picks.
=SUMIF(A2:A100, "East",
=INDEX(B2:E100, 0, G1))
Dynamic SUMIF using column header name instead of number:
G1 holds a column header like "Sales" — MATCH converts it to a column number.
=SUMIF(A2:A100, "East",
=INDEX(B2:E100, 0, MATCH(G1, B1:E1, 0)))
15
MATCH alone — find positions, check existence, locate the MAX row
MATCH does not always need to be nested inside INDEX. Its position number is useful on its own — for existence checks, for driving other formulas, and for finding which row holds the maximum or minimum value in a range.
Find the position of "James" in column A:
Returns a number like 3 if James is the 3rd row in the list.
MATCH("James", A2:A100, 0)
Check whether "James" exists anywhere in column A:
ISNUMBER returns TRUE if MATCH finds a result, FALSE if it does not.
No error handling needed — ISNUMBER converts the #N/A to FALSE cleanly.
ISNUMBER(MATCH(G1, A2:A100, 0))
Find which row contains the highest salary:
Pass MAX(C2:C100) as the lookup value — finds the row of the maximum.
MATCH(MAX(C2:C100), C2:C100, 0)
Combine with INDEX — return the name of the highest-paid employee:
=INDEX(A2:A100, MATCH(MAX(C2:C100), C2:C100, 0))
Find the last row of numeric data in a column:
MATCH(9^9, C:C, 1) finds the last number — useful for dynamic range sizing.
MATCH(9^9, C:C, 1)
✓
ISNUMBER(MATCH(...)) is one of the most useful existence-check patterns in Excel. It is faster than COUNTIF on large sorted ranges, and works for both exact and approximate matches depending on the match_type you supply.
Common INDEX MATCH Errors and How to Fix Them
#N/A — value not found
The lookup value does not exist in the lookup range. Check for extra spaces using TRIM, check for data type mismatches (text-formatted numbers will not match genuine numbers), and verify that match_type is 0. Additionally, wrap in IFERROR to display a cleaner message: =IFERROR(INDEX(..., MATCH(...)), "Not found").
Wrong result — MATCH returns an unexpected row
The most common cause is match_type defaulting to 1 instead of 0. Check that the third argument of MATCH is explicitly 0 for every formula. Furthermore, verify that the return range and the lookup range start on the same row — an off-by-one mismatch in start row causes the formula to return a value from the wrong record.
Array formula not working in Excel 2019 or earlier
Multi-criteria Boolean array formulas (Examples 6, 7, 11, 13) must be confirmed with Ctrl+Shift+Enter in Excel 2019 and earlier. Excel 365 accepts them as regular formulas. Alternatively, use the nested INDEX trick from Example 7 — this avoids array entry in all Excel versions and is the most robust approach for cross-version workbooks.
INDEX MATCH vs XLOOKUP: XLOOKUP (Excel 365 and Excel 2021 only) is simpler to write and handles most single-criterion lookups in fewer arguments. However, INDEX MATCH works in all versions from Excel 2003 onwards, supports two-way lookups natively, and remains the preferred choice for multi-criteria array formulas and cross-version compatibility.
Frequently Asked Questions
-
Why use INDEX MATCH instead of VLOOKUP?+
There are four main reasons. First, INDEX MATCH can look in any direction — the return column can be to the left of the lookup column, which VLOOKUP cannot do. Second, inserting or deleting columns never breaks the formula, because it references ranges by name rather than by position number. Third, it is often faster on large datasets since MATCH scans only the lookup column. Fourth, it handles two-way, multi-criteria, case-sensitive, and closest-match lookups natively.
-
Why is my INDEX MATCH returning the wrong value?+
The most common cause is forgetting match_type 0 in the MATCH function — it defaults to 1 (approximate match), which requires sorted data and returns wrong results on unsorted lists. Other causes include mismatched range start rows between the return array and the lookup array, data type mismatches (text vs number), or extra leading and trailing spaces. Use TRIM and VALUE to clean data before running the lookup.
-
How do I use INDEX MATCH with multiple criteria?+
Two methods work. The concatenation method joins criteria values and lookup ranges with ampersand: =INDEX(C:C, MATCH(A1&"|"&B1, ColA&"|"&ColB, 0)). The Boolean array method multiplies condition arrays: =INDEX(D:D, MATCH(1, (A:A="East")*(B:B="Widget")*(C:C=2026), 0)). Both require Ctrl+Shift+Enter in Excel 2019 and earlier. In Excel 365 they enter as normal formulas. Both methods return the first row matching all conditions.
More INDEX MATCH Questions
-
Can INDEX MATCH look up values on a different sheet?+
Yes. Use the SheetName!Range syntax in both arguments: =INDEX(Staff!C:C, MATCH(A2, Staff!A:A, 0)). Both the return range and the lookup range can point to any sheet in the workbook. For external workbooks, use [WorkbookName.xlsx]SheetName!Range — the external file must be open for the formula to return live results.
-
Does INDEX MATCH work in all Excel versions?+
Yes. INDEX and MATCH have been available since Excel 2003. The basic single-criterion pattern works identically in all versions. Multi-criteria array formulas (Examples 6, 7, 11, 13) require Ctrl+Shift+Enter in Excel 2019 and earlier — in Excel 365 they enter normally. The nested INDEX trick from Example 7 avoids array entry in all versions.
-
Should I use INDEX MATCH or XLOOKUP?+
XLOOKUP is simpler to write and covers most single-criterion lookup needs with fewer arguments. It is the better choice for new files opened only in Excel 365 or Excel 2021. However, INDEX MATCH is preferable when the file must work in Excel 2019 or earlier, when a two-way lookup is needed (INDEX MATCH MATCH), or when the advanced array techniques in Tier 3 are required. Many advanced users continue to prefer INDEX MATCH for its broader flexibility.
