You are currently offline, waiting for your internet to reconnect

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.

This article was previously published under Q324988
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.
MORE INFORMATION
Enter the following data into a blank Excel worksheet. You will use this data for the sample formula in this article.
ABC
1NameDeptAge
2Henry50128
3Stan20119
4Mary10122
5Larry30129
6
7Harry40121
8Joe10123
9Lynn30130

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
Feedback