Skip to content

The Mighty INDEX Function

The INDEX function is one of the most simple but powerful functions available in spreadsheets.

When combined with other functions such as MATCH or COUNTIF, the INDEX function is very useful. However, the INDEX function still packs quite a punch when used alone.

What Does the INDEX Function Do?

The INDEX function returns a value or list of values located at a specific position within a specified range. This is best understood with a simple example.

Let’s say that you have a list of five food items.

  • Banana
  • Grape
  • Apple
  • Toast
  • Chocolate Chip Cookie

If you would like to know which food item exists in the third position within the list, you would use the INDEX function. You would set up the INDEX function to return whatever value is third in line. It would then return the value of “Apple.”

2D Searches

The INDEX function can also return a value or set of values based on its two-dimensional position within a matrix. A matrix is simply a list of values listed in rows and columns. Take a look at the matrix below.

If you would like to know which value exists at the intersection of row 2 and column B, you could use the INDEX function to look up and return that value.

Returning Multiple Values

The INDEX function can also return multiple values instead of just one. To be more precise, the INDEX function can return all the values in a specified row or column.

Take another look at the matrix shown below.

The INDEX function can be set up to return all the values in row 3 for example. If so, it will return all of the following values.

  • Orange
  • Purple
  • Yellow

This is just one example of the how the INDEX function can return multiple values.

How to Use the INDEX Function

To use the INDEX function, you generally need three arguments. However, if you are working with a single column of items instead of a matrix you only need two arguments.

For the sake of our example, we will be using the matrix shown below once more.

For the INDEX function to work properly, it will require the following three arguments.

=INDEX(range, row, column)

The first argument is the range of cells referenced by the INDEX function. The INDEX function will only look through this range when searching for a value.

The second argument is the row of the item you want to find. This argument is any positive integer.

Finally, the third argument is the column you wish for the INDEX function to search in. Once again, this argument is any positive integer.

Putting all three arguments together, here is how you would set up the INDEX function to return the item located in the second row and second column.

INDEX(A1:C4,2,2)

The INDEX function returns a value of “White” as expected.

To get the INDEX function to return all values in column C, you would set it up this way. Notice that the middle argument (the row number) is left blank.

=INDEX(A1:C4,,3)

As you can see, the values spill over into the next few empty cells showing that the INDEX function is returning more than one value.

To get the INDEX function to return all the values in row 2, you would set it up this way. Notice that there is no third argument (the column number).

=INDEX(A1:C4,2)

Once again, the extra values spill over into the empty cells in the spreadsheet.

Although the INDEX function is fairly simple, it is a very useful function to know and understand well.

This Post Has 2 Comments

  1. I’ve never really used INDEX before. What kind of context would this be used in? Maybe an article about how it could be combined with other functions would be helpful!

    1. That is an excellent question! INDEX can be used in many different situations. In particular, the INDEX function is commonly used in conjunction with the MATCH function as a replacement for VLOOKUP. In fact, look out for our upcoming post, “The INDEX and MATCH Combination” scheduled to be released on Wednesday, November 23rd.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top