How to use INDEX Google Sheets formula?

The syntax for the INDEX formula is =INDEX(reference, [row], [column]), it has 1 required parameter and 2 optional parameters.

INDEX

The INDEX formula in Google Sheets is used to return a value or reference of a cell at the intersection of a specified row and column in a given range. The formula is commonly used to lookup and retrieve values from a table or range of cells.

How to use INDEX formula?
Examples of using INDEX formula
INDEX formula not working?
Similar formulas to INDEX

Usage

Use the INDEX formula with the syntax shown below, it has 1 required parameter and 2 optional parameters:

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

Parameters:

reference (required):
The range of cells from which to return a value.
row (optional):
The row number within the range to return a value from. If omitted, the formula returns the entire column of data.
column (optional):
The column number within the range to return a value from. If omitted, the formula returns the entire row of data.

Examples

Here are a few example use cases that explain how to use the INDEX formula in Google Sheets.

Lookup a single value from a table

By specifying both the row and column arguments, INDEX can be used to lookup and return a single value from a table of data.

Return a column of data

By omitting the row argument, INDEX can be used to return a column of data from a range of cells.

Return a row of data

By omitting the column argument, INDEX can be used to return a row of data from a range of cells.

Common Mistakes

INDEX not working? Here are some common mistakes people make when using the INDEX Google Sheets Formula:

Incorrect reference

One of the most common mistakes is providing an incorrect reference for the formula. Make sure that the reference is correct and that it includes all the necessary data.

Missing row or column parameter

Another common mistake is forgetting to provide either the row or the column parameter, or both. Make sure that you include the necessary parameters for the function to work properly.

Incorrect row or column parameter

Make sure that the row or column parameter you provide is within the range of the reference. If the parameter is outside of the range, the formula will return an error.

Using non-numeric parameters

The row and column parameters must be numeric values. If you provide non-numeric parameters, the formula will return an error.

Incorrect use of array formula

When using INDEX with an array formula, make sure that you use curly braces {} to enclose the formula. If you forget to use the braces, the formula will return an error.

The following functions are similar to INDEX or are often used with it in a formula:

MATCH
The MATCH formula is used to search for a specified value within a range and returns the relative position of the value within the range. This formula is commonly used in conjunction with the INDEX formula to retrieve a value from a specific cell in a range. The search_type parameter is optional and determines the type of match to be performed, such as exact match, less than, or greater than.
VLOOKUP
The VLOOKUP function is a lookup formula used to search for a value in the first column of a range of cells (the search key) and return a value in the same row from a specified column in that range. This function is most commonly used to look up and retrieve data from a table.
HLOOKUP
The HLOOKUP function is a lookup formula that searches for a key in the top row of a table and returns the value in the same column for a specified row. This function is commonly used to extract data from a table based on a specific criteria.

Learn More

You can learn more about the INDEX Google Sheets function on Google Support.