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

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.

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.