SheetsLessons
Lookup function

MATCH function in Google Sheets

MATCH returns the position of a value within a single row or column. It doesn't return the value itself, just where it is, which is exactly what INDEX needs.

Syntax

=MATCH(search_key, range, [search_type])
search_key
The value to find.
range
A single row or column to search.
search_type
Optional. 0 for exact match. 1 (default) finds the largest value ≤ key in ascending data, -1 the smallest ≥ key in descending data.

Practice MATCH

Task 1 of 4

At what position in the list A2:A11 does the name in D2 appear? Answer in E2.

E2
fx

Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.

MATCH examples

Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.

ABCDE
1Emp IDNameDeptEmailSalary
21042Maya PatelFinancemaya.patel@acme.co$72,000.00
31187Leo RossiSupportleo.rossi@acme.co$51,000.00
41305Ava KimSalesava.kim@acme.co$64,000.00
51411Omar AliMarketingomar.ali@acme.co$58,000.00
61520Hana TanakaSaleshana.tanaka@acme.co$69,000.00
Employee directory
FormulaResultWhat it shows
=MATCH("Ava Kim", B2:B6, 0)3Third in the list.
=MATCH("Salary", A1:E1, 0)5Which column is Salary?
=MATCH(1300, A2:A6, 1)2Sorted IDs: position of the largest ID ≤ 1300.
=MATCH("Zed", B2:B6, 0)#N/ANot found returns #N/A.

Tips and common mistakes

  • Always pass 0 for names, IDs and codes. The default 1 assumes sorted data and can return wrong positions silently.
  • With 0, text keys can use wildcards: =MATCH("Ava*", B2:B6, 0).

Related functions

INDEXReturns the cell at a given row and column of a range.XLOOKUPFinds a value in one range and returns the matching item from another.VLOOKUPSearches the first column of a range and returns a value from the same row.

Learning step by step? MATCH is covered in Lesson 20: MATCH Function of the free course.