What is INDEX + MATCH + MATCH?

The two-way lookup pattern. Two MATCH functions inside one INDEX - one finds the row, the other finds the column, INDEX returns the intersection.

INDEX MATCH in Excel: Syntax, 5 Examples, VLOOKUP vs INDEX-MATCH | Sheets & Cells

Function Combo · Lookup & Reference

INDEX / MATCH — The Universal Lookup Combo

The classic power move: two functions used together. INDEX returns a value at a specified position. MATCH returns the position of a value. Combined, they replace VLOOKUP with more flexibility — look in any direction, survive column insertions, run faster on wide tables. Works in every Excel version ever shipped.

Quick answer

INDEX returns the value at a given position in a range. MATCH returns the position of a given value. Nest MATCH inside INDEX and you get a flexible lookup that can search any direction, insert-safe, working in every Excel version.

The combo

=INDEX(return_column, MATCH(lookup_value, lookup_column, 0))

Working example

=INDEX(C2:C7, MATCH("David Kim", A2:A7, 0)) → MATCH finds "David Kim" at position 2 in column A. INDEX returns the value at position 2 in column C: $141,440.

📗 Free INDEX/MATCH example workbook

8 sheets · INDEX alone + MATCH alone + the combo + look LEFT + two-way lookup + VLOOKUP conversion cheat sheet · works in all Excel versions

Download .xlsx (free) Open in Sheets

The two syntaxes — INDEX and MATCH

INDEX/MATCH is really two functions with distinct jobs. Learn them separately first, then combine.

INDEX — returns a value at a position
Argument	Type	What it does
`array`	REQUIRED	The range of cells to pull a value from. Can be a single column, single row, or a 2D grid.
`row_num`	REQUIRED	Which row to return (1 = first row). If `array` is a single row, this is the column position instead.
`column_num`	OPTIONAL	Which column to return, when `array` is a 2D grid. Omit for single-column or single-row arrays.

MATCH — returns the position of a value
Argument	Type	What it does
`lookup_value`	REQUIRED	The value to find. Can be text, number, date, or a cell reference. Case-insensitive for text.
`lookup_array`	REQUIRED	The range to search. Must be a single row or single column (not a 2D grid).
`match_type`	OPTIONAL	0 = exact match (default choice for text lookups). 1 = largest ≤ lookup (approximate, requires ascending sort). -1 = smallest ≥ lookup (requires descending sort). Always type 0 for text lookups.

The combo formula

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

MATCH finds the position. INDEX returns the value at that position.

The mental model that makes INDEX/MATCH click: think of it as two-step directions. MATCH says "the answer is in row 4." INDEX says "okay, hand me the value from row 4 of the salary column." Split into two roles — one finds the location, one fetches the value. Together they can look in any direction, unlike VLOOKUP's left-to-right restriction.

Five working examples

Every formula below is a real cell from the free workbook. Open the download and change the yellow cells to see the answers update live.

01 INDEX alone — value at a position Foundation

Understand what INDEX does by itself before combining. Give it a range and a position number.

Position	Employee Name
1	Emma Thompson
2	David Kim
3	Sofia Rodriguez
4	Michael Chen
5	Aisha Patel
6	James Wilson

=INDEX(B2:B7, 3)

→ Returns Sofia Rodriguez — the value at position 3

2D INDEX — a full grid

When INDEX targets a 2D range, use both row_num AND column_num:

=INDEX(A2:D7, 2, 3)

→ Returns the value at row 2, column 3 of the grid — David Kim's salary, $141,440

INDEX by itself is only useful when you already know the position. Usually you don't — you know a NAME and need to find its row. That's MATCH's job.

02 MATCH alone — position of a value Foundation

MATCH takes a value and a range, returns a NUMBER — the position of that value.

Lookup value	Formula	Result
Emma Thompson	=MATCH("Emma Thompson", A2:A7, 0)	1
David Kim	=MATCH("David Kim", A2:A7, 0)	2
Sofia Rodriguez	=MATCH("Sofia Rodriguez", A2:A7, 0)	3
James Wilson	=MATCH("James Wilson", A2:A7, 0)	6

The 3rd argument (match_type) controls how MATCH searches. Always use 0 for exact match on text — this is the equivalent of VLOOKUP's FALSE. Leaving it out defaults to 1 (approximate match), which requires sorted data and silently returns wrong positions otherwise.

03 Basic INDEX + MATCH combo Combo

The full pattern — VLOOKUP-style lookup, but flexible.

A · Name	B · Dept	C · Salary	D · Manager
Emma Thompson	Operations	$108,160	Sarah Chen
David Kim	Engineering	$141,440	Marcus Rivera
Sofia Rodriguez	Design	$99,840	Priya Kumar
Michael Chen	Marketing	$87,360	Diana Lee

=INDEX(C2:C5, MATCH("David Kim", A2:A5, 0))

→ Returns $141,440 — David Kim's salary

=INDEX(B2:B5, MATCH("David Kim", A2:A5, 0))

→ Returns Engineering — same lookup, different return range

=INDEX(D2:D5, MATCH("David Kim", A2:A5, 0))

→ Returns Marcus Rivera — same lookup, another return range

To get a different field, you change the RETURN RANGE — not a column number like in VLOOKUP. Insert a new column between B and C tomorrow? Nothing breaks. INDEX/MATCH is column-insertion safe when you use range references or named ranges.

04 Look LEFT Combo

Given a salary, return the employee name — the return column is to the LEFT of the lookup column. VLOOKUP fundamentally can't do this.

A · Name	B · Dept	C · Salary
Emma Thompson	Operations	$108,160
David Kim	Engineering	$141,440
Sofia Rodriguez	Design	$99,840
Michael Chen	Marketing	$87,360

=INDEX(A2:A5, MATCH(99840, C2:C5, 0))

→ Returns Sofia Rodriguez — searched column C, returned column A (two columns to the LEFT)

MATCH searches the SALARY column (C). INDEX returns from the NAME column (A). The two ranges can be in any order, in any direction — a freedom VLOOKUP doesn't offer.

05 Two-way lookup: INDEX + MATCH + MATCH Power combo

The killer pattern. Pick a product AND a month — two MATCH functions find the row and column, INDEX returns the intersection.

Product	Jan	Feb	Mar	Apr	May	Jun
Widget A	$12,400	$13,100	$11,800	$14,200	$15,300	$14,100
Widget B	$8,700	$9,200	$8,900	$10,100	$10,800	$11,400
Gizmo Pro	$22,500	$24,100	$21,800	$23,600	$25,400	$26,100
Gizmo Lite	$6,300	$6,800	$7,100	$7,400	$7,900	$8,200

=INDEX(B2:G5, MATCH("Gizmo Pro", A2:A5, 0), MATCH("Apr", B1:G1, 0))

→ Returns $23,600

The anatomy — three moving parts

MATCH("Gizmo Pro", A2:A5, 0) → finds "Gizmo Pro" in the Product column → returns 3

MATCH("Apr", B1:G1, 0) → finds "Apr" in the Month header row → returns 4

INDEX(B2:G5, 3, 4) → returns the value at row 3, column 4 of the grid → $23,600

This pattern predates XLOOKUP by 20 years. Change either dropdown and both MATCH functions re-fire, feeding INDEX new coordinates. It's how every serious financial model built before 2019 handled matrix lookups.

Interactive playground

Try it Live INDEX + MATCH demonstration

This mirrors the live cells in the workbook. Change the yellow input → the blue answer updates instantly.

Input · Employee name

David Kim

Output · Salary

$141,440

=INDEX(Salaries, MATCH("David Kim", Names, 0))

Input · Salary to reverse-lookup

$99,840

Output · Employee name

Sofia Rodriguez

=INDEX(Names, MATCH(99840, Salaries, 0))

Download the workbook to experiment with your own values in a real spreadsheet.

Common errors and how to fix them

Because INDEX/MATCH is two functions, errors can come from either one. Six common scenarios:

Error	Why it happens	Broken → Fix
#N/A	MATCH can't find the lookup value (typo, wrong list, missing entry).	`=INDEX(Salaries, MATCH("Nobody", Names, 0))` `=IFERROR(INDEX(Salaries, MATCH("Nobody", Names, 0)), "Not Found")`
#REF!	INDEX's position argument is larger than the range size (asking for row 10 in a 6-row range).	`=INDEX(A2:A7, 10) /* only 6 rows */` `Ensure MATCH targets the right array so its output stays in range`
#N/A	Trailing space in MATCH's lookup value (invisible but breaks exact match).	`=MATCH("A100 ", SKUs, 0)` `=MATCH(TRIM("A100 "), SKUs, 0)`
#VALUE!	INDEX's row_num or column_num is text instead of a number.	`=INDEX(Grid, "David", 2)` `=INDEX(Grid, MATCH("David", Names, 0), 2)`
Wrong result	MATCH's 3rd argument omitted, data unsorted — approximate match returns wrong position.	`=INDEX(Salaries, MATCH("David Kim", Names))` `=INDEX(Salaries, MATCH("David Kim", Names, 0))`
#REF!	INDEX return array and MATCH lookup array have different row counts.	`=INDEX(A2:A10, MATCH(X, B2:B15, 0))` `=INDEX(A2:A10, MATCH(X, B2:B10, 0))`

📗 Every example above, in one workbook

Change the yellow cells, watch the formulas recalculate. Plus a VLOOKUP → INDEX/MATCH conversion cheat sheet.

Download index-match-examples-2026.xlsx

VLOOKUP vs XLOOKUP vs INDEX/MATCH — the complete comparison

The three lookup approaches, side by side. Every user searches for this comparison; here it is, all in one table:

Feature	VLOOKUP	XLOOKUP	INDEX / MATCH
Works in Excel 2019 & earlier	✓ Yes	✗ No	✓ Yes Since 2007
Works in Excel 365 / 2021+	✓ Yes	✓ Yes	✓ Yes
Look LEFT (return column left of lookup)	✗ No	✓ Yes	✓ Yes
Built-in "if not found"	✗ No Needs IFERROR	✓ Yes	✗ No Needs IFERROR
Column-insertion safe	✗ No	✓ Yes	✓ Yes
Two-way lookup (row × column)	◐ Nested MATCH	✓ Nested XLOOKUP	✓ INDEX+MATCH+MATCH
Reverse search (last match)	✗ No	✓ Yes	◐ Requires array formula
Wildcards	✓ Automatic	✓ match_mode 2	✓ In MATCH's 3rd arg
Faster on wide tables	✗ No	✓ Fastest	✓ ~30% faster than VLOOKUP
Available in Google Sheets	✓ Yes	✓ Since Aug 2022	✓ Yes
Learning difficulty	Easy	Easy	Medium (two functions)
Formula readability	Simple	Cleanest	Nested

The recommendation: If your team is all on Excel 365 or 2021+, use XLOOKUP — it's the cleanest. If some colleagues are on Excel 2019 or older, use INDEX/MATCH — same flexibility, universal compatibility. Use VLOOKUP only for simple lookups where beginner-friendliness matters more than long-term maintenance.

Related functions

Excel version compatibility

INDEX and MATCH have been in Excel since the earliest versions. They work everywhere spreadsheets exist.

Platform	Supports INDEX/MATCH?	Notes
Excel 365 (Windows & Mac)	✓ Yes	Full support
Excel 2021	✓ Yes	Full support
Excel 2019	✓ Yes	Full support
Excel 2016 & earlier	✓ Yes	Since Excel 5.0 (1993)
Excel for the web	✓ Yes	Identical behavior
Excel on iPad & iPhone	✓ Yes	Full support
Google Sheets	✓ Yes	Same syntax, same behavior
LibreOffice Calc	✓ Yes	Same syntax, all versions
Apple Numbers	✓ Yes	Same syntax

When to use INDEX/MATCH vs. alternatives

Use INDEX/MATCH when…

You need broad compatibility AND flexibility. The workbook will open in mixed Excel versions AND you need left-lookups or two-way lookups. INDEX/MATCH is the only combination that gives you both.
You're building a financial model that must survive column insertions. Analysts insert columns constantly. INDEX/MATCH with named ranges is immune.
Speed matters on wide tables. On tables with 20+ columns, INDEX/MATCH reads only two columns per lookup, ~30% faster than VLOOKUP scanning across.
You need a two-way lookup. The INDEX + MATCH + MATCH pattern is the cleanest way to look up an intersection of a row AND a column.

Use XLOOKUP instead when…

Everyone opening the file is on Excel 365 or 2021+. XLOOKUP is more readable and has built-in error handling.
You want the fastest possible lookups on huge sorted data — XLOOKUP's binary-search mode beats INDEX/MATCH.
You value formula readability over compatibility.

Use VLOOKUP instead when…

The lookup is trivially simple (leftmost column, columns don't shift).
You're teaching a beginner who'll get confused by nested functions.
You're maintaining a legacy workbook where consistency matters more than perfection.

How INDEX and MATCH actually work

INDEX's algorithm

INDEX is one of Excel's oldest functions — it appeared in Excel 5.0 in 1993. When you call =INDEX(A1:A10, 5), Excel navigates directly to the 5th cell of the range and returns its value. No searching, no comparing — pure array indexing, O(1) constant time. INDEX cannot fail if the position argument is valid; it either returns a value or throws #REF!.

When the array is 2D and you provide both row_num and column_num, INDEX jumps to that grid cell directly. It's the fastest lookup primitive Excel has.

MATCH's algorithm

MATCH scans the lookup_array and returns the position (1-indexed) of the lookup_value. Behavior depends on the 3rd argument:

match_type = 0: linear scan top-to-bottom (or left-to-right for horizontal ranges), stops on first exact match. Case-insensitive for text. Supports * and ? wildcards. Returns #N/A if no match.
match_type = 1: binary search on sorted-ascending data, returns the position of the largest value ≤ lookup_value. Requires the array to actually be sorted; MATCH won't check.
match_type = -1: binary search on sorted-descending data, returns the smallest value ≥ lookup_value. Rare — mostly used for reverse-sorted price tiers.

Why the combo works so well

MATCH gives you a position number. INDEX takes a position number and returns a value. They're designed to compose. The result: =INDEX(SalaryColumn, MATCH(name, NameColumn, 0)) reads almost like English — "give me the salary at the position where the name matches."

The two-way lookup pattern

Because INDEX accepts BOTH a row_num and column_num, you can nest TWO MATCH functions inside it: one for the row, one for the column. This is INDEX+MATCH+MATCH — the canonical two-way lookup that predates XLOOKUP by 20 years and is still widely used in financial models.

Wildcards in MATCH

When match_type = 0, MATCH supports two wildcards for text lookups:

* matches any sequence of characters. MATCH("Widget*", Names, 0) finds the first name starting with "Widget".
? matches exactly one character. MATCH("SKU-00?", SKUs, 0) matches SKU-001 through SKU-009 but not SKU-010.
Prefix with ~ to search for a literal * or ?.

Performance notes

INDEX/MATCH is typically faster than VLOOKUP — 15% faster on typical tables, ~30% faster on wide tables (20+ columns), and can be dramatically faster with binary-search MATCH on very large sorted data.

Why INDEX/MATCH beats VLOOKUP on speed

Only reads two columns. VLOOKUP scans horizontally across the table until it reaches col_index_num. INDEX/MATCH only ever touches the lookup column and the return column.
INDEX is O(1) after MATCH finds the position. Once MATCH returns a row number, INDEX just jumps there — no scanning.
Binary search available. MATCH(x, sorted_array, 1) uses binary search on sorted data: 20 comparisons instead of 1 million on a 1M-row array.

When to reach for something else

If your team is on Excel 365 and speed is critical, XLOOKUP with search_mode = 2 (binary search) is slightly faster on large sorted data. For joining two tables entirely, Power Query's Merge is faster than tens of thousands of INDEX/MATCH calls and creates a cleaner, more maintainable pipeline.

How to write an INDEX/MATCH lookup from scratch

Identify the lookup value and the return
What are you searching for (name, ID, SKU) and what do you want back (salary, price, category)?
Find the two columns
The LOOKUP COLUMN (where the value lives) and the RETURN COLUMN (where the answer lives). They can be anywhere on the sheet, in any order — INDEX/MATCH doesn't care about direction.
Write MATCH first
=MATCH(value, lookup_column, 0). Test it in an empty cell — you should see a position number (1, 2, 3…), not #N/A.
Wrap in INDEX
=INDEX(return_column, MATCH(value, lookup_column, 0)). The MATCH result feeds INDEX's row_num argument. Test — you should see the correct return value.
Wrap in IFERROR for safety
=IFERROR(INDEX(return_col, MATCH(val, lookup_col, 0)), "Not Found"). Always do this in production formulas so missing values show a friendly message instead of #N/A cascading downstream.

Functions used with INDEX/MATCH

VLOOKUP XLOOKUP XMATCH OFFSET CHOOSE IFERROR IFNA IF TRIM CLEAN VALUE EXACT SMALL LARGE

🎁 Grab the free INDEX/MATCH workbook

Every example above, plus a VLOOKUP → INDEX/MATCH conversion cheat sheet for migrating your old formulas.

Download index-match-examples-2026.xlsx

Frequently asked questions

Why is INDEX/MATCH two functions?

Because they were designed to compose. INDEX predates lookup functions — it was Excel's raw array-indexing primitive. MATCH was added later to find positions. Nesting them together turned out to be more flexible than VLOOKUP because you separate "find the position" from "return the value." XLOOKUP finally combined them, but INDEX/MATCH still wins on compatibility.

Is INDEX/MATCH faster than VLOOKUP?

Usually yes — about 15% faster on typical tables, up to 30% faster on wide tables (20+ columns), and much faster if you use MATCH with binary search on sorted data. VLOOKUP scans horizontally across every column; INDEX/MATCH only reads two.

Should I use INDEX/MATCH or XLOOKUP?

If everyone opening your file has Excel 365 or 2021+, XLOOKUP is cleaner and slightly faster. If some users are on Excel 2019 or older, INDEX/MATCH is your only option among the flexible lookups — it works everywhere.

Can INDEX/MATCH look up with multiple criteria?

Yes. Two approaches: (1) Create a helper column concatenating your criteria and match on that: =INDEX(Price, MATCH(A2&B2, HelperCol, 0)). (2) Use an array formula with multiplication: =INDEX(Price, MATCH(1, (A:A=x)*(B:B=y), 0)) — press Ctrl+Shift+Enter in older Excel. The helper column approach is simpler and more maintainable.

Does INDEX/MATCH work in older Excel versions?

Yes — INDEX and MATCH have been in Excel since Excel 5.0 (1993). They work in every version, on every platform, in Google Sheets and LibreOffice. Universal compatibility is a big reason INDEX/MATCH is still popular.

Does INDEX/MATCH support wildcards?

Yes — MATCH supports * and ? wildcards when its 3rd argument is 0 (exact match). =MATCH("Widget*", Names, 0) finds the first name starting with "Widget". To search for a literal * or ?, prefix with ~.

Can INDEX return an entire row or column?

Yes. Use 0 for the argument you want to "spread" — =INDEX(Grid, 3, 0) returns the entire 3rd row as an array. In Excel 365, this spills into adjacent cells. In older versions, wrap it in another function like SUM or use it inside array formulas.

What's INDEX + MATCH + MATCH?

The two-way lookup pattern. Two MATCH functions inside one INDEX — one MATCH finds the row, the other finds the column, INDEX returns the intersection. The formula reads: =INDEX(Grid, MATCH(row_val, Rows, 0), MATCH(col_val, Cols, 0)). Standard for looking up values in a matrix like "sales for Product X in Month Y."

How is INDEX/MATCH different from VLOOKUP?

Four key differences: (1) INDEX/MATCH can look LEFT — VLOOKUP can only return columns to the right. (2) INDEX/MATCH is column-insertion safe — VLOOKUP's hard-coded col_index_num silently breaks when someone inserts a column. (3) INDEX/MATCH is ~15-30% faster on wide tables. (4) INDEX/MATCH handles two-way lookups cleanly with INDEX+MATCH+MATCH.

Can INDEX/MATCH be case-sensitive?

MATCH is case-insensitive by default. For case-sensitive lookups, replace MATCH with an EXACT-based array formula: =INDEX(B:B, MATCH(TRUE, EXACT(A:A, "David"), 0)). Press Ctrl+Shift+Enter in older Excel versions.

What if my data has duplicates?

Standard MATCH returns the FIRST match. To find the LAST match: =INDEX(B:B, LOOKUP(2, 1/(A:A=x), ROW(A:A))) — a classic trick that works in every Excel version. In Excel 365, XLOOKUP with search_mode = -1 is cleaner.

How large a range can INDEX/MATCH handle?

The same as any Excel range: 1,048,576 rows and 16,384 columns per sheet. Performance stays excellent up to hundreds of thousands of rows, especially with binary-search MATCH on sorted data (match_type = 1).

Templates that use INDEX/MATCH

See INDEX/MATCH applied in real templates from our library:

Skip the syntax. Ask in plain English.

The Sheets & Cells AI Add-in writes INDEX/MATCH, XLOOKUP, VLOOKUP, and every other formula for you — right inside Excel. Type "find the Q1 sales for Product X" and get a working INDEX+MATCH+MATCH two-way lookup, ready to paste.

Try the AI Add-in →