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.

Contents

## 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.

- This argument is the row number within the
`column`

– [Optional] – The column of the cell to return.- This argument is the column number within the
`reference`

, not the column letter.

- This argument is the column number within the

## 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.

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.

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`

.

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.

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.