Function reference /Lookup

MATCH

Run in a real sheet

Returns the position of a value within a range, rather than the value itself.

MATCH(search_key, range, [search_type])

MATCH answers "where is it?" rather than "what is it?". That position is what INDEX needs, which is why the two are almost always written together as INDEX(…, MATCH(…)).

Executed 23 Aug 2026 Google Sheets · unedited screenshot
=INDEX(C2:C6, MATCH("Cara", B2:B6, 0)) running in Google Sheets, returning 1500
The formula sits in the formula bar and its answer sits in the grid, in one frame. MATCH finds Cara in position 3 of the name column, and INDEX returns the third sales figure. Because the two ranges are independent, the lookup column can sit to the right of the result column.

Worked example

RegionRepSales
WestAna1200
EastBen900
WestCara1500
NorthDan600
EastEve1100
 
=INDEX(C2:C6, MATCH("Cara", B2:B6, 0))

Returns 1500.

Arguments

ArgumentRequiredWhat it does
search_key Yes The value to find.
range Yes A single row or single column to search.
search_type Optional 0 for an exact match. 1 (default) assumes ascending order, -1 assumes descending.

Gotchas

  • 01The default search_type is 1, not 0 — omitting it on unsorted data returns confidently wrong positions. Always pass 0.
  • 02MATCH only accepts a single row or single column. A rectangular range is an error.
  • 03It returns a position, not a value. Feeding it straight into a cell shows a number, which looks like a bug but is not.