This article explains how to use the INDEX function in Excel 365. The same steps apply to Excel 2016 and Excel 2019, but the interface will look a little different.

What to Know

  • Use INDEX: Select cell for output > enter INDEX function. Example: =INDEX (A2:D7,6,1).INDEX with reference: Select cell for output > enter INDEX function. Example: =INDEX ((A2:D3, A4:D5, A6:D7),2,1,3).Formula: =INDEX (cell:cell,row,column) or =INDEX ((reference),row,column,area).

How to Use INDEX Function in Excel

The INDEX formula is a great tool for finding out information from a predefined range of data. In our example, we’re going to use a list of orders from a fictional retailer that sells both stationary, and pet treats. Our order report includes order numbers, product names, their individual prices, and quantity sold.

  • Open the Excel database you want to work with, or re-create the one we have shown above so that you can follow along with this example.
  • Select the cell where you want the INDEX output to appear. In our first example, we want to find the order number for Dinosaur Treats. We know that data is in Cell A7, so we input that information in an INDEX function in the following format:
  • =INDEX (A2:D7,6,1)
  • This formula looks within our range of cells A2 to D7, in the sixth row of that range (row 7) in the first column (A), and outputs our result of 32321.
  • If instead, we wanted to find out the quantity of orders for Staples, we would input the following formula:
  • =INDEX (A2:D7,4,4)
  • That outputs 15.

You can also use different cells for your Row and Column inputs to allow for dynamic INDEX outputs, without adjusting your original formula. That might look something like this:

Open the Excel database you want to work with, or re-create the one we have shown above so that you can follow along with this example.

Select the cell where you want the INDEX output to appear. In our first example, we want to find the order number for Dinosaur Treats. We know that data is in Cell A7, so we input that information in an INDEX function in the following format:

=INDEX (A2:D7,6,1)

This formula looks within our range of cells A2 to D7, in the sixth row of that range (row 7) in the first column (A), and outputs our result of 32321.

If instead, we wanted to find out the quantity of orders for Staples, we would input the following formula:

=INDEX (A2:D7,4,4)

That outputs 15.

The only difference here, is that the Row and Column data in the INDEX formula are input as cell references, in this case, F2, and G2. When the contents of those cells are adjusted, the INDEX output changes accordingly.

How to Use INDEX Function With Reference

You can also use the INDEX formula with a reference, instead of an array. This lets you define multiple ranges, or arrays, to draw data from. The function is input almost identically, but it utilizes one additional piece of information: the area number. That looks like this:

You can also use named ranges for your array.

=INDEX ((reference), row_number, column_number, area_number)

We’ll use our original example database in much the same way to show what a reference INDEX function can do. But we will define three separate arrays within that range, enclosing them within a second set of brackets.

  • Open the Excel database you want to work with, or follow along with ours by inputting the same information into a blank database.
  • Select the cell where you want the INDEX output to be. In our example, we’ll be looking up the order number for Dinosaur treats once again, but this time it’s part of a the third array within our range. So the function will be written in the following format:
  • =INDEX ((A2:D3, A4:D5, A6:D7),2,1,3)
  • This separates our database into three defined ranges of two rows a piece, and it looks up the second row, column one, of the third array. That outputs the order number for Dinosaur Treats.

What Is the INDEX Formula in Excel?

The INDEX function is a formula within Excel and other databasing tools which grabs a value from a list or table based on the location data you enter into the formula. It is typically displayed in this format:

Open the Excel database you want to work with, or follow along with ours by inputting the same information into a blank database.

Select the cell where you want the INDEX output to be. In our example, we’ll be looking up the order number for Dinosaur treats once again, but this time it’s part of a the third array within our range. So the function will be written in the following format:

=INDEX ((A2:D3, A4:D5, A6:D7),2,1,3)

This separates our database into three defined ranges of two rows a piece, and it looks up the second row, column one, of the third array. That outputs the order number for Dinosaur Treats.

=INDEX (array, row_number, column_number)

What that’s doing is designating the INDEX function and giving it the parameters that you need it to draw the data from. It starts with the data range, or a named range that you have previously designated; followed by the relative row number of the array, and the relative column number.

That means you’re inputting the row and column numbers within your designated range. So if you were to want to draw something from the second row in your data range, you would input 2 for the row number, even if it’s not the second row in the entire database. The same goes for the column input.

Get the Latest Tech News Delivered Every Day