INDEX

Syntax: =Index(Array, Row_number, [Column_number])

The Excel Index formula returns a value of the cell at the intersection of a specified row and column.

The function can be broken down into three parts:

  1. Array. Where do I want to look up the data? This is a range of cells.
  2. Row_number. What row, in the array, do I want to return?
  3. [Column_Number]. The brackets signify an optional argument; however, if Row_number is blank this argument is required. What column, in the array, do I want to return?

If both the row_num and column_num arguments are used, the Index formula will return the value in the cell at the intersection of row_num and column_num.

If you set row_num or column_num to 0, the Index formula returns the array of values for the entire column or row. In order to display the array of values, highlight a range of cells before entering the formula and then after the formula is entered press Ctrl + Shift + Enter to activate the array. You will know that the array was activated successfully because the formula will be enclosed with braces {}.

Index example:

Objective: use the index formula to return the Net Profit number for 6/30/2013 in the excel table

  1. Where do I want to look up the data? The value is located in the data table in the range B21:E33
  2. What row in the table is the value I’m looking for? 6/30/2013 is in the 7th row so set this value to 7.
  3. What column in the table is the value I’m looking for? I’m looking for the Net Profit number which is in the 4th column, so set this value to 4.

Result: In this example the index formula will return the value at the intersection of row 7 and column 4. In this case the formula returned a value of 2,625. Looking in the data table I can confirm that this is the correct value.

The Index formula may not appear to be considerably useful by itself, but as you will see in the more advanced discussions, we will be replacing the Row_number and the Column_number arguments in the Excel Index formula with other functions to allow for a more dynamic look ups.

Click here to open the excel spreadsheet shown below: Index_Example

Index_Example1