INDEX MATCH Explained: The Ultimate Lookup Formula

INDEX MATCH is a lookup formula built from two functions: MATCH finds the position of a value in one column or row, and INDEX returns the value at that same position from another range. Combined as =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), it works in every Excel version and, unlike VLOOKUP, can return a column to the left of the one you search.

What do the INDEX and MATCH functions do?

INDEX returns the value at a given position in a range, and MATCH returns the position of a lookup value within a single row or column. Neither is a lookup on its own: MATCH answers "at which position?" and INDEX answers "what is at that position?"

INDEX: Returns a Value at Position

=INDEX(array, row_num, [column_num])

=INDEX(A1:A100, 5)
Returns the value in the 5th row of A1:A100

=INDEX(A1:C100, 5, 2)
Returns value at row 5, column 2 (B5)

MATCH: Finds the Position

=MATCH(lookup_value, lookup_array, [match_type])

=MATCH("Apple", A1:A100, 0)
Returns the position of "Apple" in the range
0 = exact match (use this for most lookups)

If "Apple" is in A8, returns 8

How do you combine INDEX and MATCH in one formula?

Put MATCH inside INDEX as the row_num argument: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). MATCH finds the row where the lookup value sits, and INDEX returns the value on that row of the return range.

Basic Lookup (Replaces VLOOKUP)

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

Example:
=INDEX(C2:C100, MATCH("John", A2:A100, 0))

Find "John" in column A, return corresponding value from column C

Worked Example: Step by Step

     A        B         C
1    Name     Dept      Salary
2    Alice    Sales     52000
3    Bob      IT        61000
4    Carol    Finance   58000

=INDEX(C2:C4, MATCH("Bob", A2:A4, 0))

Step 1: MATCH("Bob", A2:A4, 0)  returns 2      (Bob is the 2nd cell of A2:A4)
Step 2: INDEX(C2:C4, 2)         returns 61000  (the 2nd cell of C2:C4, which is C3)

The position is counted from the start of the range, not the top of the sheet. Bob is on worksheet row 3, but MATCH returns 2 because the range starts at row 2, which is why the lookup range and the return range must start on the same row.

Why This is Better Than VLOOKUP

• Look left: Return column can be LEFT of lookup column
• Insert columns: Won't break if you add columns
• Smaller footprint: Refers only to the lookup and return columns, not the whole table
• Clearer: Explicitly states which columns to use

What are some practical INDEX MATCH examples?

The three patterns you'll use most are a straight lookup (find an employee, return their salary), a left lookup that returns a column sitting to the left of the lookup column, and a two-way lookup that uses one MATCH for the row and a second MATCH for the column.

Salary Lookup

Data:
A2:A20 = Employee Names
D2:D20 = Salaries

=INDEX($D$2:$D$20, MATCH(E2, $A$2:$A$20, 0))

Looks up employee in E2, returns their salary
$ makes references absolute for copying down

Look Up to the Left

Data:
B2:B50 = Product Codes (lookup column)
A2:A50 = Product Names (return column - left of lookup!)

=INDEX($A$2:$A$50, MATCH("PROD-123", $B$2:$B$50, 0))

VLOOKUP can't do this!

Two-Way Lookup (Row AND Column)

Data table:
      Jan  Feb  Mar
Alice  100  150  200
Bob    120  180  220
Carol  110  160  210

=INDEX($B$2:$D$4,
    MATCH("Bob", $A$2:$A$4, 0),
    MATCH("Feb", $B$1:$D$1, 0)
)

Returns: 180

First MATCH finds Bob's row (2)
Second MATCH finds Feb column (2)
INDEX returns value at row 2, column 2

Advanced Techniques

Because MATCH can search a calculated array and supports approximate matching, INDEX MATCH also covers lookups on several criteria at once (VLOOKUP usually needs a helper column for that), the closest value below a threshold, and returning any column, or a whole row, from a table.

Multiple Criteria Lookup

Find row where First Name = "John" AND Last Name = "Smith"

=INDEX($D$2:$D$100,
    MATCH(1,
        ($A$2:$A$100=F2) * ($B$2:$B$100=G2),
    0)
)

Enter as array formula: Ctrl + Shift + Enter (older Excel)
Excel 365: Just press Enter

The * creates AND logic (1 only when both match)

See how to enter an array formula if you're on Excel 2019 or earlier, and INDEX MATCH with multiple criteria for three or more conditions and what happens when several rows match.

Lookup Closest Match Below

Tax brackets, shipping tiers, volume discounts:

Sorted data:
A2:A10 = Income thresholds (ascending order)
B2:B10 = Tax rates

=INDEX($B$2:$B$10, MATCH(C2, $A$2:$A$10, 1))

Match type 1 = closest value less than or equal
REQUIRES sorted data in ascending order!

Return Multiple Columns

=INDEX($A$2:$E$100, MATCH("John", $A$2:$A$100, 0), 3)

Returns column 3 (C) for John's row

Change the last number (3) to return different columns:
1 = column A, 2 = column B, etc.

Use 0 to return the whole row (Excel 365/2021 spills it):
=INDEX($A$2:$E$100, MATCH("John", $A$2:$A$100, 0), 0)

Error Handling

When MATCH can't find the lookup value it returns #N/A, and INDEX passes that error through. Wrap the whole formula in IFNA (Excel 2013 and later) to replace only #N/A, or in IFERROR to replace any error; IFNA is safer because IFERROR also hides #REF! and #VALUE! errors caused by mistakes in the formula itself.

Safer (only replaces #N/A):
=IFNA(INDEX($C$2:$C$100, MATCH(E2, $A$2:$A$100, 0)), "Not Found")

Basic error handling:
=IFERROR(
    INDEX($C$2:$C$100, MATCH(E2, $A$2:$A$100, 0)),
    "Not Found"
)

Shows "Not Found" instead of #N/A error

Check if exists first:
=IF(COUNTIF($A$2:$A$100, E2) > 0,
    INDEX($C$2:$C$100, MATCH(E2, $A$2:$A$100, 0)),
    "Not in list"
)

Should you use INDEX MATCH, VLOOKUP or XLOOKUP?

Use XLOOKUP when everyone who opens the file has Microsoft 365 or Excel 2021 or later, and INDEX MATCH when the workbook must also work in older versions; both look in any direction and survive inserted columns. VLOOKUP is fine for quick lookups to the right, but its column number breaks when columns move. For typical sheets the three run at about the same speed, so choose on compatibility and readability (see VLOOKUP vs XLOOKUP).

Feature	VLOOKUP	INDEX MATCH	XLOOKUP
Direction	Right only	Any direction	Any direction
Column changes	Breaks	Resilient	Resilient
Speed (typical data)	About the same	About the same	About the same
Compatibility	All versions	All versions	365 & 2021+
Two-way lookup	Only with MATCH as col_index_num	Yes	Yes (nested XLOOKUP)

Why does INDEX MATCH return #N/A or the wrong value?

Usually because the lookup value is not an exact match for what is in the cell (a number stored as text, a trailing space), MATCH's match_type was left out, or the lookup and return ranges don't line up. MATCH ignores case, so "apple" and "Apple" are not the problem.

• Misaligned ranges: The MATCH range must cover the same rows as the INDEX range. With a lookup range of A2:A100 and a return range of C1:C99, every result comes from the row above the match, with no error to warn you.
• Relative references: Without $ signs, the ranges slide down as you copy the formula and later rows miss the data.
• Omitted match_type: MATCH(E2, A2:A100) defaults to 1 (approximate match), which assumes ascending order and can return a wrong row without any error. Type the 0 for an exact match.
• Unsorted data with match type 1/-1: 1 needs ascending order and -1 needs descending order; otherwise the results are wrong.
• Numbers stored as text: The number 1001 does not match the text "1001". Convert one side, with E2&"" (number to text) or VALUE(E2) (text to number).
• Extra spaces: "Smith " with a trailing space does not equal "Smith". Clean the data with TRIM. The VLOOKUP not working fixes apply to MATCH too.