MATCH Function in Excel
MATCH finds a value in a range and returns its position — first, second, tenth. It's not a lookup by itself but the essential partner to INDEX, powering the classic INDEX/MATCH pattern that ranked as Excel's best lookup for two decades before XLOOKUP arrived.
What it does: Searches lookup_array for lookup_value and returns its position (a number, starting at 1). Doesn't return the value itself — just where it is. Pair with INDEX to get the value at a corresponding position.
What MATCH does
Give MATCH a value to find and a range to search. It returns the position of that value in the range: 1 if the value is in the first cell, 2 if the second, and so on. If the value isn't found, MATCH returns #N/A.
Position by itself isn't immediately useful — you usually want the corresponding value from another column. That's where INDEX comes in: INDEX takes a position and returns the value at that spot. Together they're the classic Swiss-army knife of Excel lookups.
=INDEX(return_column, MATCH(lookup_value, lookup_column, 0)). This does what VLOOKUP does, but works with any column layout (including looking left), doesn't break when columns are inserted, and is faster on large ranges. In 2026, XLOOKUP handles the same cases more cleanly — but INDEX/MATCH still dominates in older workbooks you'll inherit.
Syntax breakdown
lookup_value Required
What to search for. Can be a number, text, logical value, or a cell reference. Text matching is case-insensitive.
lookup_array Required
The range to search in. Must be a single row or single column — MATCH doesn't handle 2D ranges. Cannot be a rectangular block.
match_type Optional
How to match: 1 (default) finds the largest value less than or equal to the lookup — requires the array sorted ascending. 0 is exact match — no sorting required. -1 finds the smallest value greater than or equal — requires descending sort. Always use 0 unless you have a specific reason not to.
5 real-world examples
Example 1: Find the position of a name in a list
Names in A2:A10. Find where "Chen" appears:
Result: 4 — "Chen" is the 4th name in the list. Case-insensitive: matches "Chen", "CHEN", or "chen".
Example 2: The classic INDEX/MATCH lookup
Look up an ID in column A, return the value from column D (which is to the left in some layouts):
=INDEX(D2:D100, MATCH(F2, A2:A100, 0))Result: The D-column value for the row where A matches F2. Unlike VLOOKUP, D can be anywhere — left of A, right of A, doesn't matter.
Example 3: Two-way lookup (row AND column)
Grid with regions as rows and months as columns. Find sales for "East" in "March":
=INDEX(B2:M11, MATCH("East", A2:A11, 0), MATCH("March", B1:M1, 0))Result: The cell at that intersection. Two MATCHes locate row and column; INDEX pulls the value. Powerful pattern for cross-tab data.
Example 4: Approximate match for tiered pricing
Column A has sorted quantity breaks (10, 50, 100, 500), column B has prices. Find the price tier for quantity 75:
=INDEX(B2:B5, MATCH(75, A2:A5, 1))Result: The price for the 50-tier (largest break ≤ 75). The 1 as third argument enables approximate matching. Requires the lookup array sorted ascending — miss this and results are silently wrong.
Example 5: Check if a value exists in a list
Test whether "Alice" appears anywhere in a name column:
=NOT(ISNA(MATCH("Alice", A:A, 0)))Result: TRUE if found, FALSE if not. MATCH returns #N/A when the value isn't found; ISNA turns that into TRUE, which NOT flips. Excel's idiomatic "does this exist" test.
Common errors and how to fix them
Value not found in the array. Common causes: trailing spaces (use TRIM), numbers stored as text (convert with VALUE), or truly missing data. Test with =ISNA(MATCH(...)) and handle with IFERROR if the missing case is expected.
The lookup_array is a rectangular range (multiple rows AND multiple columns) — MATCH only accepts a single row or single column. Fix: narrow the range to one dimension, or use INDEX/MATCH combined for 2D lookups.
Almost always because match_type was omitted (defaults to 1, requires sorted data) and the data isn't actually sorted. Always specify 0 for exact match unless you have unsorted data and specific reason to use approximate.
MATCH is always case-insensitive. For case-sensitive matching, use SUMPRODUCT: =SUMPRODUCT((EXACT(A:A, "alice"))*(ROW(A:A)))/SUMPRODUCT(--EXACT(A:A, "alice")), or use XMATCH's case-sensitive mode in modern Excel.
MATCH vs XMATCH vs VLOOKUP vs XLOOKUP
| Function | Best for | Watch out for |
|---|---|---|
| MATCH | Legacy INDEX/MATCH; all Excel versions | Default match_type is 1 (not 0) — dangerous |
| XMATCH | Modern Excel; wildcards, reverse search | Excel 2021+ only |
| VLOOKUP | Simple left-to-right lookups | Fragile if columns shift; can't look left |
| XLOOKUP | Modern lookups in one function | Excel 2021+ only |
| INDEX/MATCH | Any-direction lookups on older Excel | Two functions instead of one |
Version compatibility
Download the practice workbook
Every example above, plus the classic INDEX/MATCH template for two-way lookups.
Related functions
Frequently asked questions
Why is my MATCH returning the wrong position?
99% of the time: you omitted the third argument, so match_type defaulted to 1 (approximate match, requires sorted data). Add , 0 to the end. Every MATCH should explicitly specify exact or approximate — never rely on the default.
Can MATCH search a whole table (2D range)?
No — MATCH only accepts a single row or single column. For 2D searches, use two MATCHes (one for row, one for column) combined with INDEX: =INDEX(table, MATCH(row_value, row_labels, 0), MATCH(col_value, col_labels, 0)).
Should I use MATCH or XMATCH in new formulas?
XMATCH if you have Excel 2021 or newer. It defaults to exact match (safer), supports wildcards natively, has a reverse-search option, and reads more clearly. Stick with MATCH only when the workbook must run on older Excel versions.
How do I use MATCH with wildcards?
Set match_type to 0 and use wildcards in lookup_value: =MATCH("Ali*", A:A, 0) finds "Alice" or "Alicia". Use ? for single character, * for any characters. To match literal * or ?, precede with tilde: "~*".
What does the -1 match_type do?
Finds the smallest value greater than or equal to lookup_value, but requires lookup_array sorted in descending order. Rarely used in practice — most tiered-pricing scenarios use 1 (ascending) instead. Avoid unless you have a genuine descending-sorted dataset.
Why does INDEX/MATCH beat VLOOKUP?
Three reasons: (1) INDEX/MATCH can look left (VLOOKUP can't); (2) INDEX/MATCH doesn't break when columns are inserted (VLOOKUP references column numbers that shift); (3) INDEX/MATCH is faster on large datasets. In modern Excel, XLOOKUP wins on all these dimensions with cleaner syntax.
Can MATCH return multiple positions?
Not in one call — MATCH returns the position of the first occurrence only. For all matching positions, use FILTER with ROW: =FILTER(ROW(A:A), A:A="target") in Excel 365. Or use an array formula with SMALL and IF in older versions.
Build any lookup pattern faster — with the Sheets & Cells AI Add-in
Describe your lookup in plain English and the Add-in writes MATCH, INDEX/MATCH, or XLOOKUP with correct arguments — including the exact match specification MATCH so easily gets wrong. All inside Excel.
Learn about the Add-in →