Lookup and reference

MATCH function playground

Returns the position of a value in a single row or column.

The playground runs in your browser. Nothing you type is sent anywhere.

How MATCH works

MATCH returns a number: where the value sits in the list, counting from 1. It doesn't return the value itself.

Use 0 for an exact match almost every time. The default, 1, assumes the list is sorted and can return a position even when the value isn't there.

=MATCH(lookup_value, lookup_array, [match_type])

Argument	What it means
`lookup_value`required	The value to find.
`lookup_array`required	A single row or column to search.
`match_type`optional	0 exact match. 1 (default) largest value ≤ lookup_value in an ascending list; -1 smallest value ≥ it in a descending list.

Formula	What it does	Result
`=MATCH(I1,A2:A9,0)`	Position of the ID in I1 in the list.	4
`=MATCH("IT",C2:C9,0)`	Where the first IT employee is.	3
`=MATCH("Tom*",B2:B9,0)`	Wildcards work with 0: the first name starting with Tom.	8
`=INDEX(E2:E9,MATCH(I1,A2:A9,0))`	Use the position to get the start date.	2/14/2022

Leaving out match_typeThe default is 1 (approximate), which needs a sorted list. Write 0 for exact.

Searching a 2D rangelookup_array must be one row or one column.

The workbook has the same sample data and formulas, plus a practice sheet.