How to use the MATCH formula in Google Sheets

Written by Valentine Schelstraete

Feb 13, 2017

In Google Sheets, the MATCH formula gives us the relative position of an item in a range of cells. The INDEX MATCH formula combination is a great and more dynamic alternative to the VLOOKUP formula. We will first explain the MATCH formula, see the snapshot below to follow the step-by-step explanation. The position of Evan is 5 within the range of cells A1 through A6.

List of Names in Google Sheets

What if we place the cells as shown in the below snapshot (B4 through B9)? The relative position of “Evan” would still remain 5.

Index Match Formula: List Placed in Different Cells

That is exactly what the MATCH formula is set out to do – return the relative position of an item (‘Evan‘) in a range of cells (A1:A6 or B4:B9).

Syntax

MATCH(search_key, range, search_type)

  • search_key – is the item that the MATCH formula searches within the range of cells. It can be a pure text (‘Evan‘), or a cell reference (like A7), or even a function that returns a string or a number (like LEFT(“Mike Johnson”,8) or DATE(2017,1,1))
  • range – is the group of cells where the MATCH formula searches for the item (search_key). This must be a one-dimensional array, i.e. either a range with a single column or a single row.
  • search_type – is an optional input that directs how the MATCH formula should search for the search_key in the range. This takes in three different values:
    • 1, is the default value (i.e. when no input is provided against search_type). Going with this option, Google Sheets assumes that the range of cells aresorted in ascending order, and accordingly returns the largest value less than or equal to search_key.
    • 0, specifies Google Sheets that it must go for an exact match. This is the ideal option to go with if the range of cells are not sorted in any order.
    • -1, as one would guess, is the exact opposite of 1. This option assumes that the range of cells are sorted in descending order, and returns the smallest value greater than or equal to search_key.

Examples

Within column A, I have a test data in ascending order, on which I’ve tried a few variations of the MATCH formula.

Variations of MATCH Formula with Sample Data

What do we see in column C, if we sort data in column A in descending order? Let’s find out.

Sample Data and Match Formula

That was with the numeric values. How about using the MATCH formula with text values? Since we can’t exactly define the ‘less than’ and ‘greater than’ values for text forms, we usually go with the search_type option 0, that tells Google Sheets to go for an exact match. Following are a few examples.

Index Match Formula: Text Values in Google Sheets

Use case: INDEX MATCH formula combination

Perhaps the most powerful use of MATCH formula in Google Sheets is when we use it along with the INDEX formula, in order to lookup values. But we already have VLOOKUP formula in Google Sheets for this purpose, don’t we? We have an example below:

VLOOKUP Formula Example in Google Sheets

The VLOOKUP formula in D2 looks up 1432 in the ID column (as it the leftmost in the range A2:B15), and from the row where it finds 1432, it fetches the value located in the second column (i.e. column B), while assuming the data is not sorted. So far, so good. But we have two critical problems with using VLOOKUP in Google Sheets.

Problem # 1: Static cell referencing.

What happens when we insert a new column between the first and second columns? Let’s try that.

Index Match Formula: Static Cell Referencing

You’ll notice the returned value is not ‘Charlie’ anymore. Because it was a semi-static formula. Google Sheets updated the second parameter to reflect the new range, but it did not accordingly change the column index (third parameter) when we added a new column before the ‘Name‘ column.

Problem # 2: Lookup column is always the left most

Situations may arise where we might have to lookup values from a column (ID) that is not the leftmost, as shown below.

Index Match Formula: Name Location ID

What if we move the ID column to make it the left most? Good idea. But, that isn’t an ideal approach. Because there can be data layout or presentation specifications that do not allow us to re-arrange columns. What, in such a situation, is the solution? The INDEX MATCH formula combination comes to our rescue. Here’s the combination syntax, followed by a few examples.

INDEX(reference, MATCH(search_key, range, search_type))

Index Match Formula

As we can see, unlike VLOOKUP, the combination works even if the lookup column is not the left most (first three examples). Not surprisingly, it also works like VLOOKUP, when the lookup column is the left most.

Also, let’s see whether the formulas still hold good, if we introduce a new column in between.

Index Match Formula: Same Data with a New Column

Thankfully, yes, they hold good. As soon as we introduced a new column (State), Google Sheets updated the references automatically to accommodate this change. Therefore, the INDEX & MATCH formula combination is much more flexible and versatile than the already popular and powerful VLOOKUP formula. To learn more about the INDEX formula and its use, check out the following blog post: How to use the INDEX formula in Google Sheets.

You may also like…

Share This