Search
Clear search
Close search

MATCH function

Returns the relative position of an item in a range that matches a specified value.

Parts of a MATCH formula

A MATCH formula is formatted as `=MATCH(search_key, range, [search_type])`.

Part Description Notes
`search_key` The value to search for.
• For example, `42`, `"Cats"`, or `I24`.
`range` The one-dimensional array to be searched.
• If a range with both height and width greater than 1 is used, `MATCH` will return `#N/A!`.
`[search_type]`

The manner in which to search.

• `search_type` is optional. `search_type `is `1` by default.
• `1`, the default, causes `MATCH` to assume that the range is sorted in ascending order and return the largest value less than or equal to `search_key`.
• `0` indicates exact match, and is required in situations where `range` is not sorted.
• `-1` causes `MATCH` to assume that the range is sorted in descending order and return the smallest value greater than or equal to `search_key`.

Sample formulas​

`MATCH("Sunday",A2:A9,0)`

`MATCH(DATE(2012,1,1),A2:F2)`

Notes

`VLOOKUP`: Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.

`HLOOKUP`: Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified cell in the column found.

`INDEX`: Returns the content of a cell, specified by row and column offset.

Examples

Returns the relative position of an item in an array that matches a specified value based on differnet `search_type`.

Mary is a Docs & Drive expert and author of this help page. Leave her feedback below about the page.