What is INDEX MATCH in Excel?

INDEX MATCH is a combination of two functions: MATCH finds the position of a value in a range, and INDEX returns the value at that position in another range. Together they perform a lookup more flexibly than VLOOKUP — they can look left, work with any column arrangement, and never break when columns are inserted or deleted.

Why is INDEX MATCH better than VLOOKUP?

INDEX MATCH can look in any direction (VLOOKUP only looks right), does not require a column number that breaks when columns are inserted, defaults to exact match unlike VLOOKUP which defaults to approximate match, handles values over 255 characters, and is generally faster on large datasets.

How do I write a basic INDEX MATCH formula?

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). For example, to look up a price in column B by product name in column A: =INDEX(B2:B100, MATCH("Widget", A2:A100, 0)). The 0 in MATCH specifies exact match.

Should I use INDEX MATCH or XLOOKUP?

Use XLOOKUP if you have Excel 365 or Excel 2021 — it is simpler to write and read. Use INDEX MATCH if you need compatibility with Excel 2019 or earlier, or if you are building formulas that will be shared with users on older versions. Both are superior to VLOOKUP.

INDEX MATCH in Excel: Complete Guide (Better Than VLOOKUP)

Quick Syntax

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

' Example — look up price by product name:
=INDEX(B2:B100, MATCH("Widget", A2:A100, 0))

The 0 in MATCH means exact match. Always use 0 for lookups by name, ID, or code.

How INDEX MATCH Works

INDEX MATCH is two functions working together, not one. Understanding each one separately makes the combination click:

MATCH — Finds Position

MATCH(lookup_value, lookup_array, match_type) searches a row or column for a value and returns its position number — not the value itself.

=MATCH("Widget", A2:A100, 0)
' Returns 5 if "Widget" is in the 5th cell of A2:A100

Think of MATCH as the person who finds which row the item is in.

For the official MATCH reference, see Microsoft's MATCH documentation.

INDEX — Returns a Value by Position

INDEX(array, row_num, [col_num]) returns the value at a specific row (and optionally column) of a range.

=INDEX(B2:B100, 5)
' Returns the value in the 5th row of B2:B100

Think of INDEX as the person who fetches the item once they're told which row to look in.

Together: INDEX(range, MATCH(...))

MATCH finds the row number; INDEX uses that number to retrieve the value from the return range:

=INDEX(B2:B100, MATCH("Widget", A2:A100, 0))
' Step 1: MATCH finds "Widget" in A2:A100 → returns 5
' Step 2: INDEX returns the value at row 5 of B2:B100 → "£12.99"

Why INDEX MATCH Beats VLOOKUP

1. Can Look Left

VLOOKUP can only return values to the right of the lookup column. INDEX MATCH has no such restriction:

' Return the Product Code (column A) for a Price (column C)
' — impossible with VLOOKUP, trivial with INDEX MATCH
=INDEX(A2:A100, MATCH(D2, C2:C100, 0))

2. Never Breaks When You Insert Columns

VLOOKUP uses a column number (e.g., the 3rd column of the range). If you insert a new column in your table, the 3rd column becomes the 4th — and your VLOOKUP silently returns the wrong data with no error. INDEX MATCH references column ranges directly, so it adjusts automatically.

3. Exact Match by Default

VLOOKUP's fourth argument defaults to TRUE (approximate match) — a frequent source of wrong results. MATCH's third argument, when set to 0, always means exact match. A pattern like MATCH(x, range, 0) is unambiguous.

4. Handles Long Strings

VLOOKUP cannot match values over 255 characters. MATCH has no such limit.

5. Works in All Excel Versions

XLOOKUP is newer and even cleaner, but it requires Excel 365 or 2021. INDEX MATCH works in Excel 2003, 2007, 2010, 2013, 2016, 2019, 2021, and 365.

Common INDEX MATCH Patterns

Basic Lookup (Replaces VLOOKUP)

=INDEX(B:B, MATCH(A2, C:C, 0))

With Error Handling

=IFERROR(INDEX(B:B, MATCH(A2, C:C, 0)), "Not found")

Two-Way Lookup (Row and Column)

' Products in column A, Months in row 1, data in B2:M50
' Find sales for a specific product and month:
=INDEX(B2:M50, MATCH(A52, A2:A50, 0), MATCH(B52, B1:M1, 0))

Find the Last Match (Reverse Lookup)

' Return the most recent price for a product that appears multiple times
=INDEX(B:B, MATCH(2, 1/(A:A="Widget"), 1))

This is an array pattern: 1/(A:A="Widget") creates an array of 1s and errors, and MATCH finds the last 1. Press Ctrl+Shift+Enter in older Excel versions to enter as an array formula.

Case-Sensitive Lookup

' XLOOKUP and VLOOKUP are not case-sensitive — INDEX MATCH can be:
=INDEX(B:B, MATCH(TRUE, EXACT(A:A, "WIDGET"), 0))

Enter with Ctrl+Shift+Enter in Excel 2019 and earlier. In Excel 365, it works as a regular formula.

Look Up and Return Multiple Columns

' Return both Price and Stock (columns B and C) for a product in column A
=INDEX(B2:C100, MATCH("Widget", A2:A100, 0), 0)

The 0 as the column argument tells INDEX to return all columns of the matched row. Requires Excel 365 to spill automatically.

MATCH Third Argument: 0, 1, or -1?

0 — Exact match. Use this for names, IDs, codes. The lookup array does not need to be sorted.
1 — Approximate match (ascending). Finds the largest value ≤ lookup_value. Array must be sorted A→Z or smallest to largest. Good for tiered rate tables.
-1 — Approximate match (descending). Finds the smallest value ≥ lookup_value. Array must be sorted Z→A or largest to smallest.

Always use 0 for exact lookups. Omitting the argument defaults to 1 (approximate), which can return silent wrong results if the list is not sorted correctly.

INDEX MATCH vs XLOOKUP

If you have Excel 365 or 2021, XLOOKUP is simpler to write and easier to read. INDEX MATCH is the right choice when:

Your workbook is shared with people on Excel 2019 or earlier
You need case-sensitive matching (XLOOKUP cannot do this)
You need a two-way lookup with dynamic row and column matching
You're building formulas inside a legacy model that already uses INDEX/MATCH throughout

Why Is My INDEX MATCH Returning #N/A?

The most common causes:

Data type mismatch: The lookup value is a number but the lookup column contains text-formatted numbers. Use =VALUE() to convert, or check with =ISNUMBER().
Extra spaces: =MATCH(TRIM(A2), B:B, 0)
Wrong match_type argument: If you omitted the 0, MATCH is using approximate match. Add 0 explicitly.
The value genuinely doesn't exist: Confirm with =COUNTIF(lookup_range, lookup_value) — if it returns 0, the value is not in the range.

The ExcelErrorFinder audit tool locates every #N/A error in your workbook across all sheets.