Excel MATCH Function

MATCH searches a row or column for a value and returns the position of that value as a number, counted from the start of the range. It doesn't return the value itself, and it doesn't return the cell address. Just a number: 1st item, 14th item, 302nd item. That distinction trips up almost everyone the first time they use it, and it's the reason MATCH is almost always paired with INDEX rather than used on its own.

Available in: all Excel versions, including Excel 365, Excel 2021, Excel 2019, and every version back to Excel 2007. MATCH has been a core function for decades — it isn't a 365-only addition, unlike its newer sibling XMATCH, which is covered further down.

Syntax

=MATCH(lookup_value, lookup_array, [match_type])

Parameter	Required	Description
lookup_value	Yes	What you want MATCH to find inside lookup_array — a number, text, or a cell reference holding either. Data type matters: a number stored as text ("4482") will not match a genuine number (4482) even though they look identical on screen, and this is one of the most common causes of a #N/A result.
lookup_array	Yes	A single row or single column to search. MATCH can't search a two-dimensional range.
match_type	No	0 for exact match. 1 finds the largest value less than or equal to lookup_value (array must be sorted ascending). -1 finds the smallest value greater than or equal to lookup_value (array must be sorted descending). Defaults to 1 if omitted.

Basic Example

You're managing a parts inventory with SKUs in column A, running from row 2 to row 313. You need to know where a specific SKU sits in that list before you can pull its price with INDEX.

=MATCH("SKU-4482", A2:A313, 0)

// "SKU-4482"  — the value you're searching for
// A2:A313     — the range MATCH searches, one column only
// 0           — exact match required

If SKU-4482 sits in row 187 of your worksheet, MATCH returns 186 — its position within A2:A313, not row 187 and not the SKU itself. Position 1 is A2, not A1. That offset matters the moment you try to feed this number into anything else.

How MATCH Works

MATCH returns a position, not a value

This is the core mechanic and the one beginners miss. =MATCH("SKU-4482", A2:A313, 0) doesn't return "SKU-4482" — you already know that value, you typed it. It returns the count of rows from the top of the range to where that value sits. On its own, MATCH is nearly useless. Paired with INDEX, that number becomes the key to pulling any field from that row.

Exact match (0) vs. approximate match (1 and -1)

0 finds the value that equals lookup_value exactly. 1 (the default) finds the largest value that's less than or equal to lookup_value, and requires the array sorted ascending. -1 finds the smallest value greater than or equal to lookup_value, and requires the array sorted descending. Use 0 for IDs, names, and SKUs. Use 1 for tiered lookups — tax brackets, commission thresholds, grade cutoffs — where you want "closest below" rather than "identical."

MATCH only searches one row or one column

Feed it a two-dimensional range like A2:C313 and it doesn't average across columns or search all of them. It errors. This is why two-way lookups need two separate MATCH calls, one for the row position and one for the column position, not one MATCH covering both dimensions.

MATCH is case-insensitive

"widget", "Widget", and "WIDGET" all match each other. If your worksheet distinguishes SKU-001 from sku-001 as different items, plain MATCH won't tell them apart — you'll need EXACT, covered below. That workaround wraps EXACT inside MATCH as an array formula, and how you confirm it depends on your Excel version: automatically on Enter in Excel 365, or with Ctrl+Shift+Enter in older versions. The full formula is in the Gotchas section.

With duplicates, MATCH stops at the first hit

If lookup_array contains the value three times, MATCH returns the position of the first occurrence only, working top to bottom (or left to right). It never returns the second or third instance, and it never returns a count. For that, use COUNTIF.

Common Use Cases

Feeding INDEX a row position

This is what MATCH exists for. INDEX needs a row number to pull data from; MATCH supplies it dynamically instead of you hardcoding one.

=INDEX(prices, MATCH("SKU-4482", sku_list, 0))

// MATCH finds where SKU-4482 sits in sku_list
// INDEX returns the price sitting in that same row of "prices"

Change the SKU in the lookup cell and both functions recalculate. No hardcoded row number to update.

Assigning a tier with approximate match

A sales team gets commission rates based on revenue bands, and the band table is sorted ascending: 0, 25000, 50000, 100000.

=MATCH(C4, commission_bands, 1)

// C4               — a rep's monthly revenue
// commission_bands — the sorted band thresholds
// 1                — find the largest threshold <= C4

If a rep brought in $67,000 against bands of 0 / 25,000 / 50,000 / 100,000, this returns position 3 — the 50,000 band — because it's the highest threshold that doesn't exceed the rep's actual number.

Checking whether a value exists at all

Sometimes you don't need the position, you need a yes/no. Wrapping MATCH in ISNUMBER converts the position (or the #N/A) into a Boolean.

=ISNUMBER(MATCH(B2, active_customers, 0))

// Returns TRUE if B2 shows up anywhere in active_customers
// Returns FALSE instead of #N/A if it doesn't

This pattern is common in data validation and conditional formatting rules, where you want a clean TRUE/FALSE rather than a raw position number.

Two-way lookup with INDEX and two MATCH calls

Pulling a value from a table by matching both a row label and a column header requires two MATCH functions, one for each axis.

=INDEX(sales_grid, MATCH(D2, region_list, 0), MATCH(E2, month_headers, 0))

// D2           — region name, matched against region_list (the row labels)
// E2           — month name, matched against month_headers (the column labels)
// sales_grid   — the full data block the two positions point into

Both MATCH ranges need to line up with the dimensions of sales_grid exactly. If region_list covers 40 rows but sales_grid only covers 38, the row and column positions land on the wrong cells with no warning — the formula just returns a number from the wrong row.

Handling Errors

MATCH returns #N/A when it can't find a match. This is the only error MATCH throws on its own.

Common causes of #N/A:

• The lookup value genuinely isn't in the array
• match_type is 0 but the value has trailing spaces, or one side is text ("4482") and the other is a number (4482)
• match_type was left at its default of 1, and the smallest value in the array is still larger than lookup_value — nothing qualifies
• lookup_array spans more than one row and one column

=IFNA(MATCH(B2, sku_list, 0), "Not found")
=IFNA(MATCH(B2, sku_list, 0), 0)   // useful when feeding the result into INDEX

Notes & Gotchas

What does the MATCH function do in Excel?

MATCH searches a single row or column for a specified value and returns that value's relative position as a number. It doesn't return the value, the cell address, or any data from other columns — just the position, counted from the start of the range you gave it.

How do you get an exact match with the MATCH function?

Set the third argument to 0. Without it, MATCH defaults to 1 (approximate match), which requires sorted ascending data and can silently return the wrong position on a table that isn't sorted. Typing 0 explicitly is the single most important habit for using MATCH correctly.

Why does MATCH return #N/A even though the value is clearly in the range?

The most common cause is match_type defaulting to 1 on unsorted data — MATCH gives up and returns #N/A once the approximate-match logic can't locate a qualifying value. The second most common cause is a text-versus-number mismatch, where "4482" in your lookup cell doesn't match 4482 stored as a number in the array. Trailing spaces from copied data cause the same silent failure.

Is the MATCH function case-sensitive?

No. MATCH treats "Widget" and "widget" as identical values and returns whichever one it encounters first in the array. If your data relies on case to distinguish entries, MATCH alone won't catch that difference.

How do you make MATCH case-sensitive?

Wrap it with EXACT inside an array formula: =MATCH(TRUE, EXACT(lookup_value, lookup_array), 0). In Excel 365, dynamic arrays handle this automatically on Enter. In Excel 2019 and earlier, you need to confirm the formula with Ctrl+Shift+Enter or it returns an error instead of a position.

How is MATCH used together with INDEX?

MATCH supplies the row or column number, and INDEX uses that number to pull the actual value from the table. Neither function is especially useful alone for lookups — MATCH gives you a position with no data attached, and INDEX needs a position to know where to look. Together they replicate what VLOOKUP does, plus the ability to look left of the lookup column, which VLOOKUP can't do.

Why do INDEX MATCH MATCH formulas break when row and column ranges don't align?

Both MATCH functions need to search ranges that correspond exactly to the dimensions of the data block INDEX is pulling from. If your row-label range covers 40 rows and your data grid covers 38, the position numbers MATCH returns don't line up with real rows in the grid, and INDEX silently returns a value from the wrong cell. There's no error here — just a wrong answer that looks plausible. Always confirm the row range, column range, and data block all cover the identical number of rows and columns.

What is the difference between MATCH and XMATCH?

XMATCH is the newer version, available in Excel 365 and Excel 2021, and it defaults to exact match instead of approximate — the opposite of MATCH's risky default. MATCH, by contrast, runs in every Excel version back to 2007. XMATCH also searches in reverse order, supports wildcard matching without an array formula, and can run a binary search for speed on large sorted ranges. MATCH still works everywhere, including older files and shared workbooks, which is why it hasn't disappeared from new formulas entirely.

Related Functions

Function	Use this when...
INDEX	You need to pull the actual value that MATCH's position points to.
VLOOKUP	Your lookup column sits to the left of the data you want, and you don't need a left lookup.
COUNTIF	You need to know how many times a value appears, not just where the first one is.