How to Use the INDEX Function
To illustrate the usage of the INDEX function, we’ll take the same example we used when explaining the OFFSET function in a previous tutorial. (Click any image for a larger view.)
In this example, we have a table listing weekly expense and income reports. We would like to construct a function in which a user can enter a week number, and the expenses paid out during that week will be returned.
The data for the table is contained in columns A through C and in rows 2 through 53. So, our array for this example would be A2:C53.
Note, in this case, that the first row of the table is actually row 2 on the Excel spreadsheet. When determining what values should be used for the row_num and col_num, disregard the general spreadsheet rows and columns and use the relative positions in the table array.
We will be asking users to enter the week number in cell G5 of this spreadsheet. Note that the week number corresponds to the actual row number in the table array that contains the information for which we are looking. Thus, the row_num argument would be G5.
No matter which week is picked by the user, the expense information will always be found in the second column of the table. This gives us a col_num of 2.
Putting all of this information together, our INDEX function becomes:
INDEX(A2:C53, G5, 2)
To see how this would appear in the Excel spreadsheet, take a look at the screenshot below.
It is possible to use other functions to get this same result. For a comparison, take a look at this tutorial covering the VLOOKUP function.