INDEX
TheINDEX
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:
 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 theINDEX
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 nonnumeric parameters
The row and column parameters must be numeric values. If you provide nonnumeric 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.
Related Formulas
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 theINDEX
formula to retrieve a value from a specific cell in a range. Thesearch_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.