INDEX Function – Google Sheets

The INDEX function in Google Sheets retrieves a value from a specific cell within a range or array of cells.

Although you see a cell’s value as a result, the function returns a cell reference which, in turn, shows the value. Keep this in mind when combining this function with others.

Syntax

=INDEX(reference, [row], [column])

  • reference – The range of cells to be evaluated
  • row – [Optional] – The row number of the cell to return.
    • This argument is the row number within the reference, not the row number of the spreadsheet.
  • column – [Optional] – The column of the cell to return.
    • This argument is the column number within the reference, not the column letter.

Video Tutorial

Examples

Example 1 – Return One Cell With the INDEX Function

Let’s start with a simple example. We will take a range of values and pull one value out of it. We are looking for the value in the second row of the first column.

The INDEX function returning one cell
The INDEX Function Returning One Cell

Formula used: =INDEX(A1:B4,2,1)

The formula uses the data in A1:B4 as its reference and looks for the value in row 2 column 1. The formula returns A2, but it shows as Sally, which is the value in A2.

Example 2 – Return a Column of Values

The INDEX function can return an entire row or column if you specify 0 for the row or column in the formula. Let’s use this trick to return an entire column.

The INDEX function returning a column of cells
The INDEX Function Returning a Column

Formula used: =INDEX(A1:B4,0,2)

Using the same reference, A1:B4, we now ask the function to return the entire second column using a 0 as the row argument. Notice the formula is only in cell E1, but its output spills into the three cells below.

These two examples show using INDEX on its own. Now let’s look at an example using it with another function. If you’re used to using INDEX/MATCH, the following example won’t be what you expect.

Example 3 – Use With the GOOGLEFINANCE Function

The GOOGLEFINANCE function returns an array when you ask for historical stock information. For example, let’s retrieve the close price of GE on 12/31/2022.

The GOOGLEFINANCE function returning an array
GOOGLEFINANCE Output

Formula used: =GOOGLEFINANCE("NYSE:GE","close","12/31/2022")

The formula is in cell A1, but its output spills into A1:B2. You can use the INDEX function to return only the close price.

The INDEX function returning one cell from an array
The INDEX Function Trimming the GOOGLEFINANCE Function’s Output

Formula used: =INDEX(GOOGLEFINANCE("NYSE:GE","close","12/31/2022"),2,2)

We see only the content from the second row and the second column. Perfect!

Example 4 – INDEX/MATCH

Just kidding! If you are trying to combine the INDEX and MATCH functions for an alternative to VLOOKUP, I encourage you to use the XLOOKUP function instead! It is more powerful and easy to use than INDEX/MATCH.

Live Example in Sheets

Get your copy of the template with these examples.

Leave a Comment