# How to use the INDEX function to find data in a table in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

##### SUMMARY
This article describes how to use the INDEX function to find data in a table at the intersection of a specific row and column in Microsoft Excel.

There are two forms of the INDEX function, Array and Reference. The primary differences between the two forms are as follows:
• The Array form can return more than one value at a time. The Reference form returns the reference of the cell at the intersection of a particular row and column.
• The Array form is entered using CTRL + SHIFT + ENTER, instead of just ENTER, as with Reference.
Enter the following data into a blank Excel worksheet. You will use this data for the sample formula in this article.
 A B C 1 Name Dept Age 2 Henry 501 28 3 Stan 201 19 4 Mary 101 22 5 Larry 301 29 6 7 Harry 401 21 8 Joe 101 23 9 Lynn 301 30

### Reference Form of INDEX

1. Enter the following formula into cell E2 (or any available blank cell):
=INDEX((A2:C5,A7:C9),2,3,2)
(A2:C5,A7:C9) are the ranges where the value that you want will be found.

2 is the row number in the range where the value is.

3 is the column number in the ranges where the value is. Because there are three columns (beginning with column A), the third column is column C.

2 is the area, A2:C5 or A7:C9, where the value is. Because there are two areas specified for the range, the second range is A7:C9.
2. Press ENTER.
In the sample formula, the INDEX function returns a value at the intersection of the second row (2) and third column (C) of the range A7:C9. The value in cell C8 is 23. Therefore, the formula =INDEX((A2:C5,A7:C9),2,3,2) will return the value 23.

### Array Form of INDEX

1. Enter the following formula into cell E3 (or any available blank cell):
=INDEX(A2:C5,2,3)
A2:C5 is the range where the value that you want will be found.

2 is the row number in the range where the value is.

3 is the column number in the range where the value is. Because there are three columns (beginning with column A), the third column is column C.
2. Press CTRL+SHIFT+ENTER to enter the formula as an array formula.
In the sample formula, the INDEX function returns a value at the intersection of the second row (2) and third column (C). The value in cell C2 is 19. Therefore, the formula =INDEX(A2:C5,2,3) will return the value 19.
inf XL2002 XL2003 XL2007
Properties

Article ID: 324988 - Last Review: 02/01/2012 23:14:00 - Revision: 6.0

Microsoft Office Excel 2007, Microsoft Excel 2002 Standard Edition, Microsoft Office Excel 2003, Microsoft Excel 2010

• kbhowto KB324988