MATCH in Google Sheets: Syntax, Examples, and Tips

To find the position of an item in Google Sheets, use MATCH with an exact-match setting: if the items are in A2:A6, =MATCH("Pear", A2:A6, 0) returns the item’s position within that range. MATCH returns a position, not the matching cell’s contents; combine it with INDEX when you need to return a related value.

What MATCH does in Google Sheets

MATCH searches a single row or column for a search key and reports where it finds that key relative to the start of the range. If the key is the third entry in A2:A6, MATCH returns 3, even though the matching cell is A4. This relative position is useful because it can be passed to another function, especially INDEX.

For example, MATCH can locate a product in a list, find a department in a heading row, or determine the position of a threshold in a sorted set of numbers. It does not itself retrieve the price, date, or other information next to the match. That distinction is the source of many first-time misunderstandings: the answer 3 means “third item in the supplied range,” not “worksheet row 3.”

The function is useful in a spreadsheet when the location of an item may change. A formula can search for a label each time the data is updated instead of relying on a fixed row number. That makes formulas easier to maintain when users insert, sort, or extend records, provided the lookup range still covers the relevant data.

MATCH syntax and its search setting

The syntax is MATCH(search_key, range, [search_type]). The search key is the item to find, the range is the one-dimensional row or column to search, and the optional search type controls whether the search is exact or approximate. In Google Sheets, the omitted search type defaults to 1, so it is safer to specify 0 when you want an exact match.

0 searches for an exact match and does not require the range to be sorted.
1 finds the largest value less than or equal to the search key; the range must be sorted in ascending order.
-1 finds the smallest value greater than or equal to the search key; the range must be sorted in descending order.

The range should be a single row or a single column, such as A2:A20 or B1:G1. MATCH is not designed to search a two-dimensional block for a value and return both a row and column. For a grid, first decide which row or column should be searched, or use separate MATCH formulas to locate the row and column positions.

Find an exact match

For ordinary lookups, use 0 as the third argument. Suppose A2:A6 contains Apple, Orange, Pear, Plum, and Peach, in that order. With those entries in A2:A6, =MATCH("Pear", A2:A6, 0) returns 3 because Pear is the third item in the search range. If the text is in another cell, refer to that cell as the search key instead of typing the word into the formula.

An exact match can be anywhere in an unsorted range. That makes it the appropriate choice for product names, employee IDs, categories, and other values where the whole key should match. Exact does not mean case-sensitive: MATCH does not distinguish uppercase and lowercase letters, so Pear and PEAR are treated as equivalent for this purpose.

Be mindful of the difference between a value and text that looks like a value. A number stored as text may not match a numeric value as expected, and leading or trailing spaces can make two text entries differ. If a lookup unexpectedly returns an error, check the underlying cell values and formatting rather than assuming the visible words or digits tell the whole story.

Use approximate matches with sorted data

Approximate matching is helpful when a key falls between listed thresholds. For example, a table may contain minimum scores for rating bands. With ascending thresholds 0, 60, 70, 80, and 90 in A2:A6, and with a score of 76 in D2, =MATCH(D2, A2:A6, 1) returns 3. The third threshold is 70, the largest listed value no greater than 76.

That result is only dependable when the range is sorted in ascending order. If the values are out of order, the result may be wrong even though the formula does not clearly signal that the table is improperly sorted. The same caution applies to search type -1, which expects descending order and returns the position of the smallest value that is still greater than or equal to the key.

Approximate matching is not a fuzzy search. It does not find a near spelling, tolerate a typo, or decide that two similar product names are probably the same. It follows an ordering rule. For names and IDs, use exact matching; for ordered cutoffs, use approximate matching only after checking the sort order and the intended boundary behavior.

Return a related value with INDEX

To retrieve a value from a neighboring column, combine MATCH with INDEX. Suppose A2:A6 contains product names and B2:B6 contains their prices in the same order. With that layout, =INDEX(B2:B6, MATCH("Pear", A2:A6, 0)) finds Pear’s position in the name list and returns the value at that position in the price list. If Pear’s price is 2.5 in B4, the formula returns 2.5.

The two ranges need to line up: the first name in A2:A6 must correspond to the first price in B2:B6, the second name to the second price, and so on. If the ranges start on different records or have different lengths, the position found by MATCH may point to the wrong value or fall outside the return range.

This approach is often called an INDEX-and-MATCH lookup. It separates the task of locating the record from the task of returning its value. It can be convenient when the return column is to the left of the lookup column, or when you want to avoid building a lookup around a fixed column index.

Wildcards, capitalization, and text details

With an exact search type of 0, MATCH can use wildcards in text search keys. The asterisk represents any number of characters, while the question mark represents one character. For instance, if A2:A6 contains region labels and you want the first one beginning with North, then with those labels in A2:A6, =MATCH("North*", A2:A6, 0) returns the position of the first matching label.

To search for a literal asterisk or question mark instead of using it as a wildcard, prefix the character with a tilde. For example, a tilde changes the wildcard meaning so that the character itself can be searched for. This matters in data containing symbols, filenames, or codes where ? and * are actual characters.

Because MATCH ignores capitalization, it is not the right function by itself when upper- and lowercase letters must be treated as different keys. It also does not automatically remove spaces, punctuation, or spelling variations. Clean or standardize source data when those differences should not count, and use a method designed for case-sensitive comparison when letter case is significant.

Common MATCH problems and fixes

The most familiar failure is #N/A, which means that an exact search did not find the key. Check for extra spaces, different data types, misspellings, or a range that does not include the expected cell. Also confirm that the formula is searching the intended column rather than a nearby one. A cell can display a number while storing it as text, so comparing the underlying values may reveal a mismatch.

If an approximate result seems implausible, check the sort order and search type first. Type 1 requires ascending order; type -1 requires descending order. Choosing the wrong type or leaving the third argument out can lead to a result that appears valid but represents a different rule than the one intended.

If INDEX returns the wrong related value, verify that its return range and MATCH range cover the same records in the same order. If a formula is copied down a table, consider whether the lookup ranges should remain fixed. In Google Sheets, dollar signs make references absolute: for example, A$2:A$20 keeps the row numbers fixed when copied vertically, while $A$2:$A$20 keeps both the column and rows fixed.

For troubleshooting, inspect the formula in this order:

Confirm that the search key is the intended cell or value.
Check whether 0 is needed for an exact search.
Compare the key with the source values, including spaces and text-versus-number differences.
For approximate searches, verify both the sort direction and threshold logic.
For INDEX with MATCH, confirm the lookup and return ranges align item by item.

MATCH compared with other lookup options

MATCH is a position-finding function, so it is most useful when another formula needs that position or when you specifically need the index within a range. INDEX plus MATCH gives you a flexible lookup, but it requires joining two functions and keeping their ranges aligned.

Google Sheets also provides XLOOKUP, which can search a range and return a corresponding value directly. For a straightforward lookup, that can be easier to read because the lookup range and return range are explicit. VLOOKUP is another familiar option, but it is organized around a search in the first column of a table and a return column to its right. Choose based on the shape of the data and whether you need a position or a returned value, rather than assuming MATCH is always the shortest solution.

These functions solve related but not identical problems. MATCH alone is a good fit for locating a heading or finding the position of a cutoff. INDEX with MATCH is useful when the return location is separate from the search location. XLOOKUP may be simpler for a direct search-and-return task. Exactness, duplicate handling, range layout, and how a formula will be maintained are sensible factors in choosing among them.

Frequently Asked Questions

Does MATCH in Google Sheets return the cell value?
No. It returns the position of the matching item within the supplied range. To return a related value, combine MATCH with INDEX or use a lookup function such as XLOOKUP.

How do I make MATCH search for an exact value?
Set the third argument, search_type, to 0. For example, with values in A2:A20, use =MATCH(D2, A2:A20, 0) to search for the value in D2 without requiring the range to be sorted.

Why does MATCH return 3 when the result is in row 4?
MATCH reports a position relative to the beginning of its search range. If the range starts at row 2, its third item is on worksheet row 4, but the position returned is still 3.

Can MATCH search horizontally?
Yes. Its search range can be a single row as well as a single column. The returned number is the item’s position from the left edge of that row range.

Is MATCH case-sensitive?
No. MATCH treats uppercase and lowercase text as equivalent. If capitalization must distinguish two keys, use a case-sensitive comparison method rather than relying on MATCH alone.

Why does MATCH return #N/A?
Usually the exact key is absent from the selected range, or the visible values differ because of spaces, data types, or spelling. Check the range and the source values; if using an approximate search, also confirm the required sort order.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.