Search form

How to use Excel's INDEX function to find data in a table

Excel's INDEX function allows you to look up a cell contained in a column or table of data in an Excel worksheet, and return the value found in that cell. There are several scenarios where the INDEX function can come in handy when working with Excel. Here are some examples.

  • You have a list of values in a column or row, and you want to find out the third value in the list.
  • You have a table of data (multiple rows and columns). You want to find the value in the cell located at the junction of the third row and the fifth column in the table.

It's important to note that INDEX can seem somewhat limited when used on its own. For that reason, it is best used in conjunction with other functions such as MATCH or DAY in order to take full advantage of its power.

Excel's INDEX function syntax

Let's look at the INDEX function in use in a simple scenario. First, let's look at the syntax of the INDEX function:

The INDEX function has the following syntax. Note that there are two different versions of the INDEX function, the Array form and the Reference form. We'll be focusing on the Array form in this lesson, but I've included the Reference syntax as well, for completeness.

  • =INDEX(array,row_num,[column_num]) - this is the array form of the INDEX function.
  • =INDEX(reference, row_num,[column_num],[area_num]) - this is the reference form of the INDEX function.

Note that the use of [] around an argument means that it is optional when you are writing a formula using the INDEX function.

The three elements in the Array form of the INDEX function are as follows:

  • array refers to the range of cells you want INDEX to look in, such as A1:A10 or A1:C10.
  • row_num refers to which row you want INDEX to look in. This is just a number (although you can use a formula to find the number if you want)
  • column_num refers to which column you want INDEX to look in. It's optional and you only need it if you want to look in a column other than the first column in the array you specified.

The four elements in the Reference form of the Index function are as follows:

  • reference refers to the range of cells you want INDEX to look in. You can have more than one range of cells, e.g (A1:B10,G1:G10)
  • row_num refers to which row you want INDEX to look in, as we saw for the Array form of INDEX.
  • column_num refers to which column you want INDEX to look in. It also works the same as in the Array form.
  • area_num refers to which reference range you want to look at. If you set reference to (A1:B10,G1:G10), you would set area_num to 2 in order to tell INDEX to look in the second range.

Note that if you only define one range of cells in reference then the Reference form will work exactly the same way as the Array form.

An example of the INDEX function in action

Now let's look at a simple example of the INDEX function in action. As noted above, we are going to concentrate on the Array form of the INDEX function in this lesson.

Imagine you have a spreadsheet that show sales by the day for the last four weeks, and you want to find the sales for Wednesday in the first week. Remember - this is a simple example to illustrate how INDEX works. Your real world examples are likely to be complex than this.

  • Here's the spreadsheet with the sales data:
    Microsoft Excel - Using the INDEX function in a simple example
  • Note that Wednesday is in the third row of the table (I'm going to ignore the heading row in this example).
  • We can write the formula to find the value for Wednesday of Week 1 (241) in several ways:
    • =INDEX(B2:B8,3)
    • =INDEX(B2:B8,3,1)
    • =INDEX(B2:E8,3)
  • It's important to note that each of these formulas gives the same result.
    • In the first version, Excel assumed column 1.
    • In the second version, we explicitly stated it should look in column 1.
    • The third version is the same as the first, except we provided a bigger range of cells to look in. That doesn't matter - Excel will still look in the first column unless we specify otherwise.

Now let's get a bit more clever, and find out the sales for Sunday in Week 3

  • Note that Sunday is in the seventh row of the table and Week 3 is in the third column.
  • We will write the formula in the following way, remembering that we must now specify which column we want to look in.
    • =INDEX(B2:D8,7,3)

​​Summary of the INDEX function

Excel's INDEX function is a powerful function when you want to find a value in a specific row and column location inside a table of data. Our examples used numeric values to identify the row and column values that INDEX should work with. However, you can use other formulas inside the INDEX function, or references to other cells in your worksheet, in order to make INDEX more useful and effective in your Excel spreadsheets.

Do you have a specific example in which you want to use the INDEX function? Why not post a comment describing your scenario, and we'll see if we can add it to our list of examples in this lesson.