Skip to content
Google Sheets

MATCH function in Google Sheets

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

=MATCH(search_key, range, [search_type])

MATCH syntax and parameters

Three arguments, two required.

  • search_keyanyRequired

    The value to search for. For example, 42, "Cats", or I24.

  • rangerangeRequired

    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_typenumberOptional

    [ OPTIONAL - 1 by default ] - The manner in which to search. 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.

MATCH examples

Formulas you'll actually reuse.

  1. The row position of a customer — 0 means exact match:

    =MATCH("Acme Corp", A2:A200, 0)

    Result17

  2. Which tier band a spend amount falls in — type 1 needs ascending thresholds:

    =MATCH(B2, {0, 1000, 5000, 20000}, 1)

    Result3

MATCH in Excel

Same name — your formula ports as-is.

Try MATCH in the playground

Edit the example — nothing to install.

Preloaded with the MATCH formula from Example 1 — change anything and watch it respond.

Loading the editor…

MATCH errors

What they mean — and the fixes.

  • #N/A

    No match — with type 0 the value must match exactly; TRIM stray spaces, and check that numbers aren't stored as text on one side.

Related functions

More ways Google Sheets gets this done.