MATCH Function in Excel: Find the Position of a Value
The MATCH function returns where a value sits in a list. Exact and approximate match types, checking if a value exists, and using MATCH inside other formulas.
By Ayoub Farid · · 1 min read
MATCH tells you where something is in a list: the 1st, 2nd, 17th item. On its own that sounds abstract, but it's the engine behind flexible lookups and "does this exist?" checks.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])| match_type | Meaning |
|---|---|
| 0 | Exact match (use this most of the time) |
| 1 (default) | Largest value ≤ lookup value; list sorted ascending |
| -1 | Smallest value ≥ lookup value; list sorted descending |
Example
With Espresso, Latte, Tea in A2:A4:
=MATCH("Tea",A2:A4,0) → 3Not found returns #N/A.
Check if a value exists
=ISNUMBER(MATCH(F2,$A$2:$A$500,0))TRUE if F2 is in the list. Wrap in IF for a label:
=IF(ISNUMBER(MATCH(F2,$A$2:$A$500,0)),"On list","Missing")This is a quick way to compare two columns.
Use MATCH inside other formulas
- With INDEX for lookups: see INDEX MATCH.
- To find a column number for VLOOKUP so it survives inserted columns:
=VLOOKUP(F2,$A$1:$H$500,MATCH("Price",$A$1:$H$1,0),FALSE)Wildcards
With match_type 0, * and ? work: =MATCH("Lat*",A2:A100,0) finds the first item starting with "Lat".
XMATCH
Newer Excel and Google Sheets have XMATCH, which defaults to exact match and can search from the bottom. If you have it, it's a safer default.
Frequently asked questions
What does MATCH return in Excel?
The position of the value within the range, not the value itself.
Why does MATCH return #N/A?
The value isn't in the range exactly (check spaces and text vs numbers), or you used approximate match on unsorted data.
What is the difference between MATCH and XMATCH?
XMATCH defaults to exact match and offers more search options. It's available in newer Excel and Google Sheets.
Is MATCH case-sensitive?
No.