MATCH

Find the position of a value in a range or array with Excel's MATCH function.

Lookup & Reference
|
Excel All versions
|
Google Sheets Supported

Spreadsheet editor

Syntax

=MATCH(lookup_value, lookup_array, [match_type]) Returns: Number

Arguments

Argument Required Description
lookup_value Yes The value to search for. Can be a number, text, logical value, or a cell reference.
lookup_array Yes The single row or column of cells to search in.
match_type No How to match: 0 for an exact match, 1 (default) for the largest value less than or equal to lookup_value, or -1 for the smallest value greater than or equal to it.

About

MATCH searches a single row or column for a value and returns its position as a number, not the value itself. For example, if a name sits in the third cell of your list, MATCH returns 3. The optional match_type argument controls how the search works: use 0 for an exact match (any order), 1 for the largest value that is less than or equal to your lookup value (data sorted ascending), or -1 for the smallest value greater than or equal to it (data sorted descending).

Reach for MATCH when you care about where something is, not just whether it exists. It really shines paired with INDEX, where MATCH finds the row or column number and INDEX pulls the value from it. This INDEX/MATCH combo is a flexible alternative to VLOOKUP because it can look left as well as right and does not break when you insert columns.

If you have a newer version of Excel, XMATCH is an upgraded version of MATCH with extra search options, and XLOOKUP can often replace the INDEX/MATCH pattern altogether.

Examples

Look up a price with INDEX and MATCH

See how MATCH teams up with INDEX to pull a price from the table. MATCH finds the row of the product code, then INDEX returns the value from another column. Change the code in B7 and watch the price update.

Spreadsheet editor

Place a score into a grade band

Try a different score in B7 and watch the grade change. With match_type 1 and the thresholds sorted low to high, MATCH lands on the largest minimum score still at or below your value. Perfect for grades, tax brackets, or commission tiers.

Spreadsheet editor

Handle a missing value without errors

Watch what happens when the value is not in the list: MATCH returns #N/A on its own. Wrap it in IFERROR to show a friendly message instead. Type Paris versus Tokyo in B7 to compare.

Spreadsheet editor

Watch out for

⚠

Forgetting the match_type and getting wrong results

If you leave match_type blank, it defaults to 1, which assumes your data is sorted in ascending order. On unsorted data this returns a position that looks valid but is wrong.

→ Add 0 as the third argument for an exact match whenever your list is not sorted: =MATCH(value, range, 0). Make it a habit unless you specifically need a range lookup.

⚠

Getting #N/A when the value should be there

MATCH returns #N/A when it cannot find the lookup value. Often the culprit is extra spaces, numbers stored as text, or a slight spelling difference.

→ Check for stray spaces with TRIM and confirm both sides are the same data type. To show a friendly message instead of the error, wrap it in IFERROR.

⚠

Searching a two-dimensional range

MATCH only works on a single row or single column. Passing a block like A1:D10 returns an error or unexpected results.

→ Point lookup_array at one row or one column only. To find a value across both rows and columns, run two MATCH formulas and feed them into INDEX.

Tips & notes

MATCH returns the position within the range, not the value, and it is not case-sensitive ("apple" and "APPLE" match). With match_type 0 you can use wildcards: ? matches any single character and * matches any sequence of characters.

Common questions

What is the difference between MATCH and VLOOKUP?

VLOOKUP returns a value from a table, while MATCH returns the position of a value as a number. MATCH is usually paired with INDEX to look up values in any direction, whereas VLOOKUP only searches left to right.

Why does MATCH return a number instead of the value I searched for?

That is by design. MATCH tells you where the value is (its position in the range), not what it is. Wrap it in INDEX if you want to return the actual value at that position.

Should I use MATCH or XMATCH?

If your Excel version has it, XMATCH is the modern upgrade with better defaults (exact match first) and reverse-search options. Stick with MATCH for older versions or when you need compatibility with everyone opening the file.