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

Article translations Article translations
Article ID: 324988 - View products that this article applies to.
This article was previously published under Q324988
Expand all | Collapse all

On This Page

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.
Collapse this tableExpand this table
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.

Properties

Article ID: 324988 - Last Review: February 1, 2012 - Revision: 6.0
APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2010
Keywords: 
kbhowto KB324988

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com