The INDEX function is perhaps one of the most under-used functions in Google Sheets. However, if you know the right way to use it, you’ll find it quite handy in a lot of applications.
In this tutorial we will show you two ways to use the INDEX function in Google Sheets:
- When you want to return a cell value
- When you want it to return an array formula
We will also explain, with the help of an example, how the INDEX function can be applied to extract data from your dataset.
What does the INDEX Function Do?
The INDEX function in Google Sheets is used to extract data from a cell or range of cells.
We specify which cell or range of cells we want to extract by providing the function with a specific range, a row, and a column.
The INDEX function will return the cell or array of cells at the intersection of the specified row and column of the specified range.
Syntax of the INDEX Function in Google Sheets
The syntax for the INDEX function can be written in two ways:
INDEX(reference, [row], [column])
This is the syntax that uses the cell reference form of input. Here,
- reference is a range of cells from which we want to extract the data.
- row is the row number (within the reference range) from which we want to extract the data.
- column is the column number (within the reference range) from which we want to extract the data. This parameter is optional.
The above syntax will return the value in the cell that is at the intersection of the given row and column index within the range specified in the reference parameter.
For example, take a look at the screenshot below:
If type the following formula in a cell, it will give you the value at the cell which is at the intersection of the second row and third column of the range specified in the first parameter:
=INDEX(A2:C3, 2, 3)
The returned value would thus be 6.
Note: If you set the row or column parameter to 0, the INDEX function returns an array of values for the entire column or row, respectively.
INDEX(array, row, [column])
This is the syntax that uses the array form of input. Here,
- array is a range of cells or an array constant from which we want to extract the data.
- row is the row in the array from which we want to extract the data. This is optional if the column parameter is specified.
- column is the column in the array from which we want to extract the data. This is optional if the row parameter is specified.
If both row and column parameters are specified, the above syntax will return the value which is at the intersection of the specified row and column.
For example, consider the formula given below:
=INDEX({1, 2; 3, 4}, 1, 2)
In this formula, we provided the INDEX function with a 2×2 array as its first parameter. This array contains 1 and 2 in the first row and 3 and 4 in the second row.
The above formula uses the INDEX function to return the value at row 2 and column 3 of the specified array.
The returned value would thus be 2.
Note: If you set the row or column parameter to 0, the INDEX function returns an array of values for the entire column or row, respectively.
How to Use the INDEX Function in Google Sheets
To further understand how to apply the syntaxes shown above, let us use the sample dataset shown below:
The dataset contains employee sales information. Let us use the INDEX function to extract some valuable information from this dataset.
Using the INDEX Function to Return a Cell Value
Let us say you want to know what the total sales that Cierra Vega made were.
You can easily use the INDEX function to extract this information from the table if you know exactly which row Cierra Vega’s sales information exists.
You also need to know in which column of the selected range, the Total Sales exists.
If we select the range of cells A2:D7, the Total Sales lies in the 4th column and Cierra Vega’s information lies in the 3rdrow of the selection.
So to find out her total sales, we will use the formula:
=INDEX(A2:D7,3,4)
Your results will then be:
In this way, you can use the same formula to extract the contents of any cell in your sheet, simply by changing the references in the INDEX function parameters.
Let us extract a few more values:
Data to Extract |
Extracted Data |
Formula |
Ted Johnson’s Units Sold: |
100 |
=INDEX(A2:D7,6,2) |
Pierre Cox’s Total Sales: |
2834.98 |
=INDEX(A2:D7,4,4) |
Paula Lee’s Units Sold: |
59 |
=INDEX(A2:D7,1,2) |
Using the INDEX Function to Return an Array Formula
If you want to extract not just the value of one cell, but a whole column or row of cells, then there’s an effective way to use the INDEX function to do this.
Using the INDEX Function to Extract a Row of Data
Let us say you want to know all the information about Cierra Vega. This means you want to extract all four cells from the 3rd row of the table.
For this, you will need the INDEX function to return an array formula that will contain all 4 values of the 3rd row.
So to extract all four cells relating to the 3rd row, we will use the formula:
=ArrayFormula( INDEX (A2:D7,3,0) )
Here are the steps you need to follow:
- Select the first cell where you want the extracted results to appear.
- Make sure there are 3 empty cells next to it, so all four extracted values can appear next to each other.
- In the formula bar, type the formula shown above.
- Press the return key.
- You should now be able to see all details relating to Cierra Vega next to each other.
Note that the result that you get is an array and not actual values in the physical cells. So if you make any changes to any of the displayed values, you will get an error.
Explanation of the Formula
The formula works because you passed 3 as the row number and 0 as the column number. Since the third row in the selected range is the row relating to ‘Cierra Vega’’, you get an array containing all the contents of row 3.
Using the INDEX Function to Extract a Column of Data
You can also extract all cell values of a particular column in your range. For example, you can use the following formula to get the names of all the employees:
= ArrayFormula (INDEX(A2:D7,0,1))
Here are the steps you need to follow:
- Select the first cell of a blank column (where you want the extracted results to appear).
- In the formula bar, type the formula shown above.
- Press the return key.
- You should be able to see a list of all employee names in the new column.
Note that the result that you get is an array and not values in the physical cells. So if you make any changes to any of the displayed values, you will get an error.
Explanation of the Formula
The formula works because you passed 0 as the row number and 1 as the column number. Since the first column in the selected range is the ‘Employee Names’ column, you get an array containing all the contents of column 1.
In this tutorial, we showed you two ways of using the INDEX function. In the first method, we showed you how to extract a single cell value from a given range, while in the second method we showed you how to extract a range of cell values from a given range.
We hope you found our examples and explanations helpful and easy to understand.
Other Google Sheets tutorials you may also like: