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.

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

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 cellsare sorted 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 cellsare 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.

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

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.

**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:

The VLOOKUP formula in D2 looks up 1432 in the ID column (as it the

**Problem # 1**: Static cell referencing.

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

You’ll notice the returned value is not ‘**Charlie**’ anymore. **Name**‘ column.

**Problem # 2**: Lookup column is always the left most

Situations may arise where

What if we move the ID column to make it the left most? Good idea. But, that isn’t an ideal approach.

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

As we can see, unlike VLOOKUP, the combination works even if the lookup column is not the

Also, let’s see whether the formulas still hold

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.