Reporting & Analysis
Sep 24, 2021
TLDR
INDEX and MATCH are essential functions in Google Sheets for flexible data analysis. INDEX retrieves a value from a specific cell within a range, while MATCH finds the relative position of an item in an array. Combining these functions allows for powerful lookups, including left-lookups, not possible with VLOOKUP. You can use INDEX to fetch values and MATCH to identify positions, making them a dynamic duo for complex data retrieval in Google Sheets.
There are many functions in Google Sheets that help you perform data analysis. INDEX and MATCH functions are some examples. But what are they, exactly?
INDEX-MATCH in Google Sheets combines two different functions, namely, INDEX and MATCH. The MATCH function returns the relative position of an item within an array. The INDEX function fetches an item from an array given a specific position. When combined, they can be used as a powerful alternative to VLOOKUP in Google Sheets, primarily because they allow you to do more kinds of lookups, e.g. a left-lookup.
In this blog, you will learn how to use these functions. We will cover the following scenarios:
How to use the INDEX function in Google Sheets
The INDEX function allows you to obtain a value located at a specified position. It accepts a range of cells, index of the row, and index of the column, and returns the value of the specified cell. Now let’s take a look at how to use this function.
Step 1: Go to your spreadsheet where you want to apply the INDEX function. Keep note of the index of the cell that contains the value you want to obtain. We’ll be using the same table as above for this example. Let’s assume you want to get the name of the fruit located in the 8th row.
Go to the cell where you would like to obtain the value.
Then use this formula.
=INDEX(A1:A10,8,1)
Here, A1:A10 indicates the range of cells from which the value is returned. 8 is the index of the row and 1 is an optional value indicating the index of the column to be returned from the selected range of cells. The default value of row and column is set as 0.
You’ll now see the value located in the 8th row of the fruit column:
Unlike the MATCH function, the INDEX function allows you to use a multidimensional array range. Let’s take a look at how to use a multidimensional array range in the formula.
How to use the MATCH function in Google Sheets
The MATCH function in Google Sheets allows you to locate a specific value in your spreadsheet. It accepts a range of cells and a value and returns the relative position of that value in the specified range.
Here’s how you can use this function. For the purpose of this blog, we’ve created a table containing two columns—one containing names of fruits and the other one vegetables. Let’s assume you want to find the position of ‘Mango’.
Step 1: Go to your spreadsheet and keep note of the row or column where you want to apply the MATCH function. Here, we know Mango is a fruit so the value is more likely to be on the first column.
Step 2: Now go to the cell where you would like the position of your value to be displayed.
Step3: Type in the following formula in that cell.
=MATCH("Mango", A1:A10, 0)
Here, "Mango" is our search_key i.e. value to search for. A1:A10 is the range of cells where we want to search our value. And 0 indicates search_type which is optional. The default value of search_type is -1 that indicates the range is sorted in descending order. If the range is sorted in ascending order, you can replace it with 1. Similarly, if your range is not sorted then you can use 0 which gives you the exact match of your value.
Now you’ll get a number on your cell. This represents the position of your value in the respective column/row. If your range of cells is a row, then the number tells you the row number of the value. Similarly, if your range of cells is a column, then the number tells you the column number of the value.
However, it’s important to note that the range for the MATCH function should be either a single row or a single column. You’ll get an error if you try to use a multi-dimensional array as your range.
How to use INDEX and MATCH together in Google Sheets
INDEX and MATCH when combined together can perform more advanced lookups in Google Sheets. This combination can also be used in place of lookup functions like HLOOKUP and VLOOKUP.
For this section, we have added a new column containing a list of names in our previous table.
Now assume you want to get the fruit eaten by a person named Margaret. This can be easily done by using the combination of INDEX and MATCH functions. Let’s see how.
Step 1: Go to your spreadsheet and click on the cell where you want to get the fruit eaten by Margaret.
Step 2: Type in the following formula.
=INDEX(A1:C10, MATCH("Margaret", A1:A10, 0),2)
Here, A1:C10 is the range of the cells. The MATCH function searches the value "Margaret" from the range A1:A10 and returns its position. This position then becomes the index of the row of the value you want to obtain. Similarly, 2 is the index of the column.
You’ll now get the fruit eaten by Margaret.
Improve your DTC game. Sign up for weekly tips.
Using VLOOKUP
You can also use VLOOKUP instead of INDEX and MATCH by using either of the following formulas.
=VLOOKUP("Margaret",A1:C10,2,FALSE)
=VLOOKUP(A6,A1:C10,2,FALSE)
Here, your first argument could either be the value or a cell reference to the value. In the first example, "Margaret" is the value. In the second example, A6 is the cell reference to the value "Margaret". Then, A1:C10 is the range and 2is the column number of the value to be returned. FALSE indicates that the column to be searched is not sorted. TRUE would indicate that the column is sorted in ascending order.
This is how it would look like for the first example:
This is how it would look like for the second example:
However, VLOOKUP cannot be used when the lookup value is to the left of the value that needs to be returned. For example, say we want to find the name of the person who eats Mushrooms. We can use INDEX and MATCH to get the desired value by using the following formula:
=INDEX(A1:C10, MATCH("Mushroom", C1:C10, 0),1)
However, this cannot be done using the VLOOKUP function:
The combination of INDEX and MATCH is a very useful function. You can even use this combination as an alternative to other lookup functions and perform more advanced lookups.
