The INDEX function

The INDEX function is a lookup and reference function which will associate a unique id to each cell in the selected range based on its position from the top-left most cell, allowing you to return the value located x-rows from the top of the range, and y-columns from the left of the range.

This is the format of the INDEX function:

=INDEX(RANGE TO INDEX, ROW TO RETURN, COLUMN TO RETURN)

Here is an example of the INDEX function:

=INDEX(A1:C5, 3, 2) = returns value of cell B3 

INDEX function returning the value of the cell in the 7th row and 6th column of the range

This function is valuable when you have data where you want to reference information from different rows or columns based on a set of parameters. Some such situations are those where you would want to return a value from a column which sits to the left of the range of values you want to search through (VLOOKUP is not capable of this).

An example of a functional implementation of this would be having a complex report which you would like to simplify by only returning the information relative the to region and period you are analyzing. You know that the region you are looking for has its information stored in column F (column 6), and that the period you are looking to return is in rows 5 to 12.

=INDEX(A1:F13,{5,6,7,8,9,10,11,12},6)

*Note on Office 365 this will automatically spill the results into nearby cells, in older version of Excel this is an array formula which needs to be entered with CTL+SHIFT+ENTER*