How to Use the Excel INDEX Function

Find the information you need quickly with this handy formula

Excel databases can be as large or as small as you need them to be, but when they grow to extreme sizes, managing that data isn't always easy. Likewise finding a particular entry in a certain cell can lead to a lot of scrolling. If VLOOKUP isn't quite cutting it, Excel's INDEX formula can help you out.

Here's how to use the Excel INDEX function to find the data you need right now.

Although the screenshots for this guide are for Excel 365, the instructions work in both Excel 2019, and Excel 2016; the UI is just a little different in each.

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:

=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.

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.

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

  2. 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)
    INDEX Function in Excel
  3. 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.

  4. 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.

INDEX Function in Excel

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:

INDEX Function in Excel

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.

You can also use named ranges for your array.

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:

=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.

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

  2. 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)
  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 Function in Excel