Excel MATCH Function: find the position of a value

MATCH looks for a value and tells you its position in a list — not the value itself. “Mango” is 2nd in the list, so MATCH returns 2.

The formula

=MATCH(lookup_value, lookup_array, [match_type])

Always type the 0. Leaving it out means approximate match, which quietly returns wrong positions on unsorted data.

Example

=MATCH("Mango",A2:A4,0)

Returns 2. Not found: #N/A.

What it is really for: INDEX MATCH

MATCH finds the row, INDEX fetches the value from that row in another column:

=INDEX(C2:C100,MATCH(F2,A2:A100,0))

Looks left or right, survives inserted columns. Full lesson: INDEX MATCH.

Other uses

• Is it on the list? =ISNUMBER(MATCH(A2,$D$2:$D$50,0)) — TRUE/FALSE.
• Which column is “March”? =MATCH("March",B1:M1,0) — works across a row too.
• Wildcards: =MATCH("Man*",A2:A100,0)

Newer: XMATCH

Excel 365 has XMATCH: exact match by default and it can search from the bottom up.

#N/A but the value is there

Extra spaces or a number stored as text. See the 7 lookup causes — they apply to MATCH too.