30 Excel Functions in 30 Days: 24 – INDEX

Yesterday, in the 30XL30D challenge, we found text strings with the FIND function, and learned that it is case sensitive, unlike the SEARCH function.

INDEX Function

For day 24 in the challenge, we’ll examine the INDEX function. Based on a row and column number, it can return a value or reference to a value. We’ve already used INDEX several times, with other functions in the challenge:

• with EXACT to find name for the password with exact match
• with AREAS to find the last area in a named range
• with COLUMNS to calculate sum of last column in range
• with MATCH to find the name for closest guess in a contest

So, let’s take a look at the INDEX information and examples, and if you have other tips or examples, please share them in the comments.

Function 24: INDEX

The INDEX function returns a value or reference to a value. Combine it with other functions, like MATCH, for powerful formulas.

How Could You Use INDEX?

The INDEX function can return a value or reference to a value, so you can use it to:

• Find sales amount for selected month
• Get reference to specified row, column, area
• Create a dynamic range based on count
• Sort column of text in alphabetical order

INDEX Syntax

The INDEX function has two syntax forms — Array and Reference. With Array form, a value is returned, and with Reference form, a reference is returned.
The Array form has the following syntax:

• INDEX(array,row_num,column_num)
  • array is an array constant or range of cells
  • if array has only 1 row or column, corresponding row/column number argument is optional
  • if array has >1 row or column, and only row_num or column_num is used, array of entire row or column is returned
  • row_num – if omitted, column_num is required
  • column_num – if omitted, row_num is required
  • if both the row_num and column_num arguments are used, returns value in cell at intersection of row_num and column_num
  • if row_num or column_num are zero, returns array of values for entire column or row

The Reference form has the following syntax:

• INDEX(reference,row_num,column_num,area_num)
  • reference can refer to one or more cell ranges – enclose nonadjacent ranges in parantheses
  • if each area in reference has only 1 row or column, corresponding row/column number argument is optional
  • area_num selects range in reference from which to return row and column intersection
  • area_num – if omitted, area 1 is used
  • if row_num or column_num are zero, returns reference for entire column or row
  • result is a reference, and can be used by other functions

INDEX Traps

If the row_num and column_num don’t point to a cell within the array or reference, the INDEX function returns a #REF! error.

Example 1: Find sales amount for selected month

Enter a row number, and the INDEX function returns the sales amount from that row in the reference. Here the month number is 4, so the April sales amount is returned.

=INDEX($C$2:$C$8,F2)

Find sales amount for selected month

To make the formula more flexible, you could use MATCH to return the row number, based on the month that was selected from a drop down list.

=INDEX($C$2:$C$8,MATCH($F$2,$D$2:$D$8,0))

Example 2: Get reference to specified row, column, area

In this example, there is a named range, MonthAmts, which consists of 3 non-contiguous ranges. The MonthAmts range has 3 areas — one for each month — and there are 4 rows and 2 columns in each area. Here is the formula for the MonthAmts name:

=’Ex02′!$B$3:$C$6,’Ex02′!$E$3:$F$6,’Ex02′!$H$3:$I$6

With the INDEX function, you can return the cost or revenue amount for a specific region and month.

=INDEX(MonthAmts,B10,C10,D10)

The INDEX function result can be multiplied, as in the Tax calculation in cell F10:

=0.05*INDEX(MonthAmts,B10,C10,D10)

or, it can return a reference for the CELL function, to show the address of the result, in cell G10.

=CELL(“address”,INDEX(MonthAmts,B10,C10,D10))

Example 3: Create a dynamic range based on count

You can also use the INDEX function to create a dynamic range. In this example, I’ve created a name, MonthList, with this formula:

=’Ex03′!$C$1:INDEX(‘Ex03’!$C:$C,COUNTA(‘Ex03’!$C:$C))

If another month is added to the list in column C, it will automatically appear in the data validation drop down list in cell F2, which uses MonthList as its source.

Example 4: Sort column of text in alphabetical order

In the final example, the INDEX function is combined with several other functions, to return a list of months, sorted in alphabetical order. The COUNTIF function shows how many month names come before a specific month name. SMALL returns the nth smallest item in the list, and MATCH returns the row number for that month.

In the video, you can see the formula broken down into steps.

This formula is array-entered, by pressing Ctrl + Shift + Enter.

=INDEX($C$4:$C$9,MATCH(SMALL(
COUNTIF($C$4:$C$9,”<“&$C$4:$C$9),ROW(E4)-ROW(E$3)),
COUNTIF($C$4:$C$9,”<“&$C$4:$C$9),0))

Download the INDEX Function File

To see the formulas used in today’s examples, you can download the INDEX function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the INDEX Video

To see a demonstration of the examples in the INDEX function sample workbook, watch this short Excel video tutorial.

_____________