Excel MATCH Function — Find the Position of a Value (with Examples)

Contents

What does MATCH do?
Syntax
5 real examples
MATCH inside INDEX MATCH
MATCH vs VLOOKUP
FAQ

What does MATCH do?

MATCH returns the position (row or column number) where a value appears in a list. If "Coffee" is the third item in a column, MATCH returns 3. That position number is then typically fed into INDEX to retrieve a corresponding value from a different column — creating the INDEX MATCH lookup combination that many Excel professionals prefer over VLOOKUP.

MATCH is also useful on its own: checking whether a value exists in a list, finding the position of a maximum or minimum value, or locating a column header dynamically.

Syntax

=MATCH(lookup_value, lookup_array, [match_type])

Argument	Description
lookup_value required	The value to find. Can be text, a number, or a cell reference.
lookup_array required	The range to search in — must be a single row or single column.
match_type optional	0 = exact match (almost always correct). 1 = largest value ≤ lookup (requires sorted ascending). -1 = smallest value ≥ lookup (requires sorted descending).

💡 Always use 0

For exact matching, always specify 0 as the match_type. Omitting it defaults to 1 (approximate match on sorted data), which returns wrong results on unsorted lists.

5 real examples

Example 1

Find position of a value

=MATCH("Coffee", A2:A10, 0) ← returns 3 if Coffee is in A4

Returns the position within the range — not the row number in the sheet. A4 is position 3 in A2:A10.

Example 2

Check if a value exists in a list

=IFERROR(MATCH(A2, ValidList, 0), "Not in list")

If MATCH finds the value it returns a position number. If not, IFERROR catches the #N/A and shows "Not in list".

Example 3

Find position of the maximum value

=MATCH(MAX(B2:B20), B2:B20, 0)

Finds which row contains the highest value — then use INDEX to return the name in that row.

Example 4

Locate a column header dynamically

=MATCH("Revenue", A1:Z1, 0)

Returns the column number of the "Revenue" header. Use inside INDEX to return data from that column without hardcoding the column number.

Example 5

Two-way lookup position

=INDEX(B2:F10, MATCH(H1, A2:A10, 0), MATCH(H2, B1:F1, 0))

MATCH finds the row, MATCH finds the column, INDEX returns the value at that intersection — a two-way lookup.

MATCH inside INDEX MATCH

The INDEX MATCH combination is the most powerful and flexible lookup in Excel. MATCH finds where to look; INDEX returns what is there.

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

Unlike VLOOKUP, INDEX MATCH can look left (return values from columns to the left of the search column), handles column insertions without breaking, and is generally faster on large datasets.

MATCH vs VLOOKUP

	VLOOKUP	INDEX MATCH
Look left	❌	✅
Column insertion safe	❌	✅
Two-way lookup	❌	✅
Easier to write	✅	Slightly harder

FAQ

What does MATCH return if the value is not found?

#N/A error. Wrap in IFERROR to handle gracefully: =IFERROR(MATCH(...),"Not found").

Can MATCH search horizontally?

Yes — provide a row range instead of a column range. MATCH(H1, A1:Z1, 0) searches across row 1 and returns the column position.

Is MATCH case sensitive?

No. MATCH("coffee",A:A,0) matches "Coffee", "COFFEE" and "coffee" equally.

Should I use INDEX MATCH or XLOOKUP?

In Excel 2019 or Microsoft 365, XLOOKUP is simpler for most lookups. Use INDEX MATCH when you need a two-way lookup (row AND column simultaneously), or when working in Excel 2016 or earlier.

Practise INDEX MATCH live

ExcelPro has INDEX MATCH exercises in every specialist track. Free to start.

Try lookup exercises →

Related formulas

INDEX MATCH VLOOKUP XLOOKUP IFERROR IF INDEX

The Excel MATCH Functionexplained simply