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:

- Array. Where do I want to look up the data? This is a range of cells.
- Row_number. What row, in the array, do I want to return?
- [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**

- Where do I want to look up the data? The value is located in the data table in the range B21:E33
- What row in the table is the value I’m looking for? 6/30/2013 is in the 7
^{th}row so set this value to 7. - What column in the table is the value I’m looking for? I’m looking for the Net Profit number which is in the 4
^{th}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**