MATCH Function in Google Sheets

If you want to find where a value sits inside a range, the MATCH function in Google Sheets gives you its position as a number. You tell it what to look for and where to look, and it returns the row or column position.

In this article, I’ll walk you through five practical examples, from a basic exact-match lookup to pairing MATCH with INDEX for a full two-way lookup.

MATCH Function Syntax in Google Sheets

MATCH takes the value you’re looking for, the range to search, and how you want it matched.

=MATCH(search_key, range, [search_type])

• search_key: the value you want to find the position of.
• range: a single row or single column to search through.
• search_type (optional): how to match. 0 finds an exact match, 1 (the default) finds the largest value less than or equal to the key in a sorted-ascending range, and -1 works on a descending range.

MATCH returns a position number, not the value itself. That number is what you feed into a function like INDEX.

When to Use MATCH Function

• Find the row position of a value so you can use it inside another formula.
• Build a two-way lookup by feeding MATCH positions into INDEX.
• Locate which item holds the highest or lowest number.
• Find the right bracket for a value in a sorted tier or band table.
• Check whether a value exists in a list at all.

Example 1: Find the exact position of a value

Let’s start with the most common use. You have a list of products and you want to know the position of a specific one.

Below is the dataset with a product list in column A and the product to find in column B.

The goal is to return the position of each lookup value inside the product list.

Here is the formula:

=MATCH(B2,$A$2:$A$6,0)

The 0 as the third argument tells MATCH to find an exact match. B2 holds the product to find, and MATCH returns where it sits in A2:A6.

The first row returns 3, meaning the lookup value is the third item in the list. The rest return 1, 5, 2, and 4.

Pro Tip: Use 0 as the match type for any unsorted list. It’s the only mode that searches a list in its current order without assuming it’s sorted.

Example 2: Find the closest match in a sorted range

With match type 1, MATCH stops looking for an exact hit and instead finds the largest value at or below your key. This is handy for tier and band lookups.

Below is the dataset with sorted threshold values in column A and the amounts to place in column C.

The goal is to find which band each amount falls into based on the sorted thresholds.

Here is the formula:

=MATCH(C2,$A$2:$A$6,1)

How this formula works:

• The 1 tells MATCH to find the largest threshold that is less than or equal to the value in C2.
• The threshold list in A2:A6 must be sorted in ascending order for this to work.
• The first row returns 3, meaning the amount falls into the third band.
• The rest return 5, 1, 4, and 5, each landing in its own band.

Pro Tip: Match type 1 only works correctly when the range is sorted ascending. On an unsorted range it returns wrong positions without any error, so double-check the sort first.

Example 3: Look up a value with INDEX and MATCH

MATCH is at its best when its position feeds straight into another function. The classic pairing is with INDEX for a lookup.

Below is the dataset with product names in column A, prices in column B, and a product to look up in column C.

The goal is to return the price for each product named in column C.

Here is the formula:

=INDEX($B$2:$B$6,MATCH(C2,$A$2:$A$6,0))

How this formula works:

• MATCH finds the position of the product in C2 inside the name list A2:A6.
• The INDEX function then returns the price at that same position from B2:B6.
• The first row returns 180, the price of the matched product.
• The rest return 25, 45, 60, and 15, each matched by position.

Pro Tip: INDEX and MATCH can look left as well as right, so you can return a value from a column that sits before the lookup column. VLOOKUP and the HLOOKUP function can’t do that.

Example 4: Two-way lookup with two MATCH calls

You can use MATCH twice in one formula, once for the row and once for the column, to pull a value from anywhere in a grid.

Below is the dataset with a sales grid in A1:D5, plus a region in column E and a quarter label in column F to look up.

The goal is to return the value where the chosen region row meets the chosen quarter column.

Here is the formula:

=INDEX($B$2:$D$5,MATCH(E2,$A$2:$A$5,0),MATCH(F2,$B$1:$D$1,0))

How this formula works:

• The first MATCH finds the region’s row position in A2:A5.
• The second MATCH finds the quarter’s column position in the header row B1:D1.
• INDEX uses both positions to return the cell where they cross.
• The first row returns 240, and the rest return 200, 120, and 140.

Example 5: Find the position of the highest value

Wrap MAX inside MATCH and you can find which row holds the largest number in a column.

Below is the dataset with months in column A and sales figures in column B.

The goal is to find the position of the highest sales figure in the list.

Here is the formula:

=MATCH(MAX(B2:B6),B2:B6,0)

MAX finds the largest value in B2:B6, then MATCH looks for that value and returns its position. The 0 keeps it an exact match.

The result is 4, so the highest sales figure sits in the fourth row of the range. Swap MAX for MIN and the same pattern finds the lowest instead.

Tips & Common Mistakes

• MATCH returns a position, not a value. If you want the actual value at that spot, wrap MATCH inside the INDEX function. On its own MATCH only tells you where.
• Use match type 0 unless your data is sorted. The default match type is 1, which assumes an ascending sort. On an unsorted range it returns wrong positions silently, so pass 0 for exact matches.
• MATCH searches one row or one column only. You can’t pass a two-dimensional block as the range. For a grid lookup, use two separate MATCH calls, one for the row and one for the column, like in Example 4.

MATCH on its own just hands you a position number, which doesn’t look like much. Paired with INDEX, MAX, or a sorted band table, it becomes the engine behind some of the most flexible lookups in Google Sheets.

List of All Google Sheets Functions

Related Google Sheets Functions / Articles:

• LOOKUP Function in Google Sheets
• CHOOSE Function in Google Sheets
• ADDRESS Function in Google Sheets
• COLUMN Function in Google Sheets