You are here
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:
 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.
Hello,
I'm not sure what function to use in this situation or if it even doable.
I am using Excel to prepare letters that go to landowners whose crops were affected by recent construction. I am using a template for a mail merge to print letters to the landowners that is working beautifully. However, I want to be able to pull the information about that landowner's crops from another worksheet onto the letter. If it was a onetoone reference, it wouldn't be a difficult task.
However, some of the landowners have several parcels of land. This has been entered into the spreadsheet using an individual ID number that was used during construction. Example:
ID No. Name Crop Twp Rng Sec
H240 John Smith Soybeans 1N 14E 12
H240 John Smith Corn 1N 14E 11
H240 John Smith Corn 1N 15E 9
W569 Amy Nelson Soybeans 2N 12E 2
There are many more columns in my table that have the formulas used for calculating the offer based on type of crop, county, length, yield, etc.
My question is how can I pull the rows of information for each landowner's crops from the "Crop" worksheet onto the "Form" worksheet? When I print the letter for John Smith his three parcels of land will appear on his letter and Amy Nelson's parcel will appear on her letter, etc.
Respectfully,
Mickie