In Google Sheets, the MATCH formula gives us the relative position of an item in a range of cells. To understand this, please see the snapshot below. 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).
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 are 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 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.
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 left most 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.
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 the we might have to lookup values from a column (ID) that is not the left most, as shown below.
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 and 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))
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.
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.