Home/Formulas/Lookup/MATCH
Lookupmedium

MATCH()

Returns the relative position of a value in a range.

Syntax
fx
=MATCH(search_key, range, [search_type])
Try it
F2
→
5
Sample data · sales.csv
ABCD
1CompanyRegionQuarterRevenue
2Acme CoWestQ118,400
3Acme CoWestQ222,100
4Acme CoWestQ331,700
5Acme CoWestQ448,200
6GlobexEastQ114,200
7GlobexEastQ219,800
8GlobexEastQ323,400

Common pitfalls

  • The third arg search_type defaults to 1 (less-than approximate). Pass 0 for exact match: almost always what you want.
  • Returns the position within the range, not the value. Wrap with INDEX to actually retrieve the value.
  • Returns #N/A if not found. Combine with IFERROR for a clean fallback.
Related formulas
INDEX()
Returns the content of a cell at a given row and column in a range.
VLOOKUP()
Vertical lookup. Searches the first column of a range and returns a value from a specified column.
Used by
VLOOKUP()
Vertical lookup. Searches the first column of a range and returns a value from a specified column.
XLOOKUP()
The modern replacement for VLOOKUP. Searches any column, returns from any column.
INDEX()
Returns the content of a cell at a given row and column in a range.
More Lookup formulas →Keyboard shortcuts for Sheets →