30 Excel Functions in 30 Days: 24 – INDEX

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:

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.

Index00

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))

Find sales amount for selected month with INDEX function
Find sales amount for selected month with INDEX function

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)

Index02a

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))

Index02b

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.

Index03a

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))

Index04

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.

_____________

17 thoughts on “30 Excel Functions in 30 Days: 24 – INDEX”

  1. Hi Debra,
    thanks for your hints.
    Two questions linked to the named dynamic range.
    1. absolute vs. relative addressing
    =$C$1:INDEX(C:C,COUNTA($C:$C)))
    there’s a mix of absolute and relative addressing (see the $ signs).
    Is this intentionally and necessary?

    2. increase the range horizontally
    Is it possible to have a named range that spans all the columns of a table?
    E.g. to have a dynamic range C4:F9, where the heading might be in C3:F3.
    Any solutions?

  2. Hugo, thanks for pointing out the mixed references — that was an error, and it’s fixed now.

    Not sure exactly what you want for the table range, but perhaps something like this:

    =Sheet1!$C$4:INDEX(Sheet1!$F:$F,COUNTA(Sheet1!$F:$F)+ROW(Sheet1!$F$3)-1)

  3. Hi Debra,

    It looks like there is a typo in an “In INDEX Syntax” section.
    Instead of “The LOOKUP function has” should be “The INDEX function has”.

    It would be easier to follow the article if you show a full definition of MonthAmts named range(screenshot or just a text)

    Thank you,
    Leonid

  4. Hi Debra

    Not sure exactly what you want for the table range, but perhaps something like this:
    =Sheet1!$C$4:INDEX(Sheet1!$F:$F,COUNTA(Sheet1!$F:$F)+ROW(Sheet1!$F$3)-1)

    I mean a kind of this I use very often:
    =OFFSET($A$1,,,COUNTA($A:$A),COUNTA($1:$1))
    assuming, that there’s a table beginning in the upper left corner of the sheet.
    Because OFFSET is volotile I would like to substitute this with an INDEX combination.
    Can we adapt this to INDEX?

  5. Hi Debra!

    I have learned a lot from this post!

    Formula in example 3 also works in a cell, I thought you had to use indirect() or offset() function to return a cell range reference.
    =’Ex03′!$C$1:INDEX(‘Ex03’!$C:$C,COUNTA(‘Ex03’!$C:$C))

    There is no need anymore for volatile functions like indirect or offset when creating a dynamic array.
    Thanks for posting!

  6. […] bingo workbook, that you could use to create a set of three cards with random numbers. It uses the INDEX and MATCH functions to pull the numbers from another […]

  7. […] so we’ll create a dynamic range named RateTable, using the technique from Example 3 in the 30XL30D INDEX function […]

  8. […] we’ll take a closer look at that dynamic range, and see how the INDEX function is used to set the last cell in the […]

  9. Hi Debra!
    First of all I want to thank you for all your postings. You’re site has really helped me a lot. I just have a question on hlookup, but I’m afraid to post it here. Can I email you instead? Please give me your email add.

    thanks,

Leave a Reply to Oscar Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.