INDEX In Google Sheets

What Is INDEX In Google Sheets?

INDEX in Google Sheets allows users to retrieve the desired data from a selected row and/or column, within a cell range or a dataset, in the same or different worksheet in a workbook. The Google Sheets INDEX can derive a single cell value, the entire row or column value based on the selected dataset.

For example, consider the data of some products and their sold units. We will use the Google Sheets INDEX to retrieve the required data from a selected cell range. Select cell D2, enter the formula =INDEX(A2:B6,4,2) and press “Enter”, as shown below.

The result of this function is 5, because the row index number is 4 and the corresponding value in 2^nd column is 5, in the lookup array A2:B6.

The INDEX in Google Sheets helps users find the data value of the specified row and column data in the selected cell range.
We can insert the 2^nd and 3^rd argument values in multiple ways, namely, as a cell reference directly from the dataset, enter the cell value or get the result of the formula in a different cell and provide cell reference of the same. Remember it is always a numeric value that represents row/column number.
Ensure to provide correct row and column number. It is not the worksheets row and column number, but the selected datasets or cell ranges row and column index number.
The MATCH+INDEX function can be used as an alternative for the VLOOKUP function.
Based on the table array selected, the function can also obtain a single cell value, an entire column, etc.

INDEX Function formula

The syntax of the INDEX Function formula in Google Sheets is,

The arguments of the INDEX Function formula in Google Sheets are,

reference: It is the only mandatory argument in the formula. It is the cell range or the dataset to retrieve the data.

[row]: It is the row index number that is counted from row 1 of the selected cell range. It is an optional argument.
[column]: The column index number that is counted from column 1 of the selected cell range. It is an optional argument.

If the optional argument values are not entered, then, by default, the formula takes it as 0.

How To Use INDEX Function In Google Sheets?

We can use the Google Sheets INDEX Function in two ways, as follows:

Access from the Google Sheets ribbon.
Enter the formula in the worksheet manually.

Method #1 – Access from the Google Sheets ribbon –

Step 1: Choose an empty cell for the output – select the “Insert” tab – click the “Function” option right arrow – click the “Lookup” option right arrow –select the “INDEX” function, as shown below.

Step 2: The “INDEX” formulaappears, as shown below. Enter the argument as cell reference.

Method #2 – Enter the formula in the worksheet manually –

Step 1: Select an empty cell for the output.

Step 2: Type =INDEX( in the cell, as shown below. [Alternatively, type =I or =IND and double-click the INDEX from the Google Sheets suggestions.]

Step 3: Enter the arguments as cell values or cell references and close the brackets.

Step 4: Press Enter to view the outcome.

Examples

Let us consider some INDEX in Google Sheets examples and retrieve the value of a single cell, a column and combine the function with functions such as, COUNTA(), MATCH(), etc.

Example #1 – Return One Cell with the INDEX Function

The data below consists of continents and one of their many countries. Let us Return One Cell With the INDEX Function, i.e. the country of a continent from the cell range.

The steps to use INDEX function are,

Step 1: Select cell E2 and enter the formula =INDEX(A:B,6,2), as shown below.

[Note: We have selected both the columns instead of a cell range, just to understand that regardless of the range selection, all that matters

Step 2: Press “Enter”, to get the output shown below.

Example #2 – Return a Column of Values

The dataset given below consists of employee details such as their names, IDs, date of joining, department and salaries. We will use the INDEX function to Return a Column of Values.

The steps to retrieve the names column usingthe INDEX are as follows:

Step 1: Select cell H2 and enter the formula =INDEX(A1:F11,0,2), as shown below.

Step 2: Press “Enter” to get names column without any formatting, as shown below.

[Note: We have selected the entire dataset and the column index number is given as 2. We have inserted the 2^nd argument value as 0, or else we will get only that row’s employee name,]

Example #3 – Using INDEX with the COUNTA function

For the same data of Example 2, we will only consider the employee names, ID and department. We will retrieve the needed information using the INDEX+COUNTA and this combined formula takes the COUNTA’s result depending on which argument it is placed in the INDEX formula, either the 2^nd or the 3^rd, and returns the most recent row/column of the selected data.

Also, we will execute the functions separately and combined to verify that the answer is same.

The steps to retrieve the needed info using the INDEX and the COUNTA are as follows:

Step 1: To use the COUNTA function, select cell E3, enter the formula =COUNTA(A2:B4) and press “Enter”, as shown below.

Step 2: To use the INDEX function, select cell E5. Enter the formula =Index(A1:C11,6,3) and press “Enter”, as shown below.

Step 3: Now, we have got the answer as Peter for the INDEX function, because we have given the row index as 6. The result of COUNTA value is also 6. Let us see if we get the same value as “Peter” with the INDEX+COUNTA function combo. We can enter the formula as follows,

Cell reference of formula result =INDEX(A1:C11,E2,3) or
Formula combination =INDEX(A1:C11,COUNTA(A2:B4),3)

Therefore, to use the INDEX+COUNTA function, select cell E8, enter the formula =Index(A1:C11,E2,3) and press “Enter”, as shown below.

Output Observation:

Range is selected as desired, here, entire dataset A1:C11.
The COUNTA() function counts selected data within the range.
The INDEX formula takes the number derived from the COUNTA() formula result. Since we added in the row index number argument, we got the extracted results from row 6.
The 3^rd argument value is 3, indicating that the result must be from column 3 or column C.

Example #4 – INDEX with MATCH function in Google Sheets

Let us consider Example 2 again with only the employee names, ID and departments. We will retrieve the needed information using the INDEX+MATCH which are a striking combination because it acts as an alternative for the VLOOKUP function.

The steps to retrieve the needed info using the INDEX and the MATCH are as follows:

Step 1: To use the MATCH function, select cell F2. Enter the formula =MATCH(E2,B2:B11,0) and press “Enter”, as shown below.

Step 2: To use the INDEX function, select cell F5. Enter the formula =INDEX(A2:C11,8,3) and press “Enter”, as shown below.

Step 3: To use the INDEX+MATCH function, select cell F8, enter the formula =INDEX(A2:C11,8,3) and press “Enter”, as shown below.

Now, we understand the result that the employee ID302 is in the 9^th row in the selected cell range, and the corresponding employee name is “Caroline”.

Step 4: For our reference let us check if the INDEX+MATCH is an alternative to the VLOOKUP function. Therefore, select cell B13, enter the formula =VLOOKUP(B9,B2:C11,2,0) and press “Enter”, as shown below.

We notice that the VLOOKUP retrieved the ID302 employee’s name as “Catherine”, same as the “INDEX+MATCH” result.

Important Things To Note

If the COUNTA() function value is more than the dataset range’s rows count, then we will get “#NUM” error.
If the optional argument values are not entered, then, by default, the formula takes it as 0. Then, the output will be the cell range selected without formatting.
We get the #REF error, if the cell reference provided is modified or deleted.
The function will return a #N/A error if the lookup value is not the same in the array.

Frequently Asked Questions (FAQs)

1. Why is my INDEX in Google Sheets not working?

The INDEX in Google Sheets isn’t working because of the following reasons:
• The argument valuesor the cell reference are not found and we get the #Ref error.
• We have not provided the 2^nd and 3rd optional argument. By default, it is considered as 0 and the date range selected will be returned as the extracted result.
• The dataset or the lookup table array is updated, modified or deleted.
• As the row or column valueis not found in the selected cell range.

2. What is an alternate way to insert the INDEX in Google Sheets?

We often forget in which category a function falls, here, the “INDEX” function. Then, we can insert the function as follows:
Choose an empty cell – select the “Insert” tab – click the “Function” option right arrow – click the “All” option right arrow – select the “INDEX” function, as shown below.
FAQ-2
However, as always, entering the function manually is the best way to avoid confusion.

3. Where else can we find the INDEX in Google Sheets?

Alternatively, we can find the Functions icon to insert the INDEX in Google Sheets by following the path shown below.
Choose an empty cell – click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
FAQ-3
A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
FAQ-3-1
Here, click the “Functions” option – click the “All” option right arrow – select the “INDEX” function, as shown below.
FAQ-3-2

Download Template

This article must be helpful to understand the INDEX in Google Sheets, with its formula and examples. You can download the template here to use it instantly.

INDEX-In-Google-Sheets Download

INDEX In Google Sheets