An alternative to VLOOKUP in Google Sheets.
There exist many functions in Google Sheets that help you perform data analysis. MATCH and INDEX functions are examples of such functions. These functions can seem very simple with limited applications when used on their own. However, when combined, they can be very powerful.
In this blog, you will learn the usage of these functions. We will cover the following scenarios:
- How to use the MATCH function
- How to use the INDEX function
- How to use INDEX and MATCH together in Google Sheets
How to use the MATCH function
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 the INDEX function
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.
- 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 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.
You can also use VLOOKUP instead of INDEX and MATCH by using either of the following formulas.
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.
However, VLOOKUP can only be used when the lookup value is to the left of the value that needs to be returned. Let’s take an example to exhibit a case where VLOOKUP would not work. 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.