The INDEX function in excel returns the value of a cell at a given location (position in a row and column). It works as follows: =INDEX (array, row_num, [col_num], [area_num])
where array is the range(s)/table(s) of values to search in, row_num is the number row (counting from the start of the array) you are looking to pull, col_num (optional) is the number of columns over from the start of the range you’d like to pull data from, and area_num (optional) is used to determine which range to use in the lookup, if more than one range is specified.
For example:
The equation to answer the second question (What is the major of the 6th person) is: =INDEX(B4:D11,6,3)
The area_num criteria is used in a situation like the following:
Now that I have 2 ranges of data, I must specify which one to used based on the data I wish to pull. The equation for the second question is : =INDEX((B4:C11, B14:C21), 6,2,1 )
The INDEX function is often paired with the MATCH function. The MATCH function returns the placement (index) of information you are looking for. When nested inside INDEX it is very powerful.
For example:
The result of the MATCH function in this example is 3 and it is used to as the row location to lookup in the INDEX function.
On a larger scale, the INDEX and INDEX(MATCH) functions are extremely helpful and powerful tools when manipulating data in Microsoft Excel.