INDEX Function in Google Sheets

INDEX in Google Sheets returns the value of a cell within a range, based on the row and column offsets you provide. On its own, INDEX retrieves a value at a known position. Combined with MATCH, it becomes a flexible alternative to VLOOKUP that can look up in any direction.

Parameters

Parameter	Required	Description
reference	Yes	The range of cells to return a value from. Can be a single range like A1:C10 or a multi-area reference.
row	No	The row number within reference to return. Row 1 is the first row of the range, not the sheet. If omitted, column is required.
column	No	The column number within reference to return. Column 1 is the first column of the range. If omitted, row is required.

If you provide only row, the range should be a single column or INDEX returns the entire row. If you provide only column, the range should be a single row or INDEX returns the entire column.

Examples

Return a value at a specific position

Get the value in the 3rd row and 2nd column of A1:C10:

=INDEX(A1:C10, 3, 2)

This returns the value in cell B3 (row 3, column 2 of the range).

Combined with MATCH for a flexible lookup

Look up an employee name in column A and return their department from column C:

=INDEX(C2:C100, MATCH("Sara Lee", A2:A100, 0))

MATCH finds the row position of “Sara Lee” in column A. INDEX then returns the value from that same row in column C.

Return an entire row

Omit the column argument to return all values from a row:

=INDEX(A1:D10, 5)

This returns the entire 5th row of the range as an array. You can use this inside other functions that accept arrays.

Common Errors

#REF! --- The row or column number exceeds the dimensions of reference. If your range is A1:C5 (5 rows, 3 columns), a row of 6 or column of 4 returns this error.

#VALUE! --- A non-numeric value was passed as the row or column argument. Both must be positive integers or zero.

Tips

INDEX and MATCH together can look up values to the left, which VLOOKUP cannot do. If your result column is before your search column, INDEX MATCH is the right choice.

When you pass 0 as the row or column, INDEX returns the entire column or row as an array. This is useful inside SUM, AVERAGE, or other aggregate functions.