INDEX Function in Google Sheets

The INDEX function in Google Sheets, on the face of it, might seem limited in its possibilities, but when used in conjunction with other functions, such as the MATCH function, it can easily compete and excel compared to the VLOOKUP function.

The VLOOKUP function can only return a value when the lookup value is to the left of the value you want to find. However, when combined, the INDEX and MATCH functions can be used regardless of where the lookup value is located within the dataset. This makes the INDEX and MATCH combined functions much more versatile.

However, it’s worth understanding how the INDEX function works in isolation before we get to that point. Once you've mastered it, the INDEX function will undoubtedly become one of your most used functions.

What is the INDEX Function?

The INDEX function finds the content of a cell in a range specified by a specific row and column number relative to the range of data.

Using this table below to demonstrate the concept, the highlighted area in blue is the range B3:E7; if we require the output value as the intersection of the third row and the third column in this range, we arrive at the highlighted cell in yellow to get the value of ‘Yes’.

Google indexes the range B3:E7, and we simply give the function a position in the table to fetch the data, just like coordinates on a map.

INDEX Syntax

The syntax for the INDEX function requires a reference which is the range to be indexed; this is the first argument.

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

The row and column arguments are optional; we can either specify a specific row and column number relative to the dataset, leave them blank, or set them to zero. More on this later.

INDEX Function

Let’s see what this looks like when writing the function out.

First up, we’ll begin by typing out the function name and choosing the range to be indexed; this is referred to as the reference in the function arguments.

=INDEX(B3:E7

Next, we’ll move on to the row argument, which is optional. We’ll see what this looks like later when you leave this option out.

We need to add a comma to move into the next argument and specify how many rows down in this range we want to go. It’s important to note that it’s the number of rows in the selected range B3:E7 we’re referring to, not the rows in the entire sheet.

=INDEX(B3:E7,3

You can see the third row down in this range is the fifth row in the sheet, but we’re only concerned with the rows and columns specific to this range of selected data.

Lastly, we need to specify the column argument; again, we’re only concerned with the columns in this range of selected data.

=INDEX(B3:E7,3,3

The result is displayed above the formula, so all we need to do now is close the parentheses to complete the function.

=INDEX(B3:E7,3,3)

Here are a few more examples of the INDEX function collecting a value from different locations within the table.

Index and Return an Entire Row

There will be occasions when it’s helpful to pull an entire row of data from a dataset, and this is one of the valuable things about the index function, so let’s look at that now.

First up, we’ll begin by typing out the function name and choosing the range to be indexed, just as before; this is referred to as the reference in the function arguments.

=INDEX(B3:E7

Suppose we want to get row 2 of this dataset, so we’ll start with adding a comma to move into the row argument and then type ‘2’ for the row followed by another comma.

Now we’re in the column argument, which is optional, as alluded to earlier. Therefore we can leave this blank, or if you prefer, you can just specify zero; either way works just fine.

=INDEX(B3:E7,2,)

=INDEX(B3:E7,2,0)

Boom, we have the entire row returned; nice!

Index and Return an Entire Column

Just like the last example, there will be occasions when pulling an entire column of data from a dataset is helpful, so let’s check that out.

First up, we’ll begin by typing out the function name and choosing the range to be indexed, just as before; this is referred to as the reference in the function arguments.

=INDEX(B3:E7

Suppose we want to get the first column from this dataset, so we’ll start with adding a comma to move into the row argument and then type ‘0’ or leave it blank for the row followed by another comma. Either way works just fine; it’s personal preference, I guess.

=INDEX(B3:E7,,

=INDEX(B3:E7,0,

Now we’re in the column argument. Next, we’ll choose the first column by entering 1 for this argument. All we need to do now is close the parentheses to complete the function.

=INDEX(B3:E7,0,1)

Easy, right?

Dynamic Index

You could make this dynamic by referencing row and column numbers from another cell; let’s see how that works.

Everything is essentially the same as before, but now we’re specifying the row and column numbers in another cell and using that cell reference within the function.

=INDEX(B3:E7,D11,E11)

D11 Contains the row with a value of 5

E11 Contains the column with a value of 2

Now you can simply change these cells to pull different data from the table depending on what you’re looking for.

The Power of INDEX with COUNTA

As mentioned at the start, the INDEX function in isolation is limited because you’re unlikely to know the row and column you want. Don’t get me wrong; it’s not useless, but it comes into its own when used with other functions like MATCH and COUNTA, for example. We touched on this in another tutorial for COUNT and COUNTA functions, so check that out to see how you can retrieve the last value in a column. However, we can go one step further by creating a dynamic list and retrieving a group of results at the top or bottom of the list by combining functions.

Dynamic List

We first need to specify the starting point of the range as a separate element in the function, which in our case is B3 and combine this with the COUNTA. In that case, this tells the formula to begin at B3, and the COUNTA simply counts the total number of rows in the range, so all rows are returned.

=B3:INDEX(B3:B,COUNTA(B3:B))

This is interesting because if more data is added to the original table, then this formula will return them dynamically without you having to lift a finger

Return the First Three Rows

Using this same technique, we can collect the first x number of rows. We simply specify the starting point with the B3: at the beginning, followed by the index and the number of rows you want to be returned.

=B3:INDEX(B3:B,3) for 3 rows, or =B3:INDEX(B3:B,5) for 5 rows, for example.

Return the Last Three Rows

Returning the last x rows is slightly more involved but absolutely possible thanks to the INDEX and COUNTA functions.

Here is the formula used to get the last three rows:

=INDEX(B3:B,COUNTA(B3:B)-2):INDEX(B3:B,COUNTA(B3:B))

The first part of this formula is essentially indexing column B; then the COUNTA counts all the values in the range, which is 7 in our case, and then we deduct 2 from this count to get 5 or the fifth position in the range and third from the end.

=INDEX(B3:B,COUNTA(B3:B)-2)

The second part of the formula does the same as the first, except it’s not deducting anything, so it essentially counts 7 values in the range.

INDEX(B3:B,COUNTA(B3:B))

The colon separating the two parts is necessary as it joins the two parts together. Therefore, the first part starts at the third from the last position, and the last part of the formula begins at this point to return everything else.

The great thing about this is you can keep adding data to the original table, and this formula will always collect the last three entries or however many you specified in the formula.

Conclusion

I hope this demonstrates the diversity of the INDEX function; it truly is a wonderful tool to master.

Feel free to play around with the INDEX Function example sheet used in this tutorial; enjoy!