30 Excel Functions in 30 Days: 24 – INDEX

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

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

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)

Index01a

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

Index01b

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, you can watch this short Excel video tutorial.

YouTube link: Excel INDEX Function Gets Value from Row and Column

_____________

You may also like...

15 Responses

  1. Hugo says:

    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. Leonid Koyfman says:

    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. Thanks Leonid, I’ve fixed the typo, and added the formula for the MonthAmts named range.

  5. Hugo says:

    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?

  6. One way, using 30000 as an arbitrary number of rows:

    =Sheet1!$A$1:INDEX(Sheet1!$1:$30000,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

  7. Oscar says:

    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!

  8. Hi Oscar
    You’re welcome! Creating a dynamic range with the INDEX function takes a bit of practice, if you’re used to OFFSET, but it’s worth the effort.

  9. Contextures Blog » Excel Bingo Card Random Number Code says:

    […] 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 […]

  10. Contextures Blog » Excel Function Friday: HLOOKUP Finds Current Price says:

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

  11. Contextures Blog » Excel Function Friday: INDEX for Dynamic Range says:

    […] 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 […]

  12. Amee Go says:

    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,

  13. @Amee, my email address is on the About page:

    ddalgleish AT contextures.com

  14. Aries says:

    Hi Debra,

    How to calculate composite index?

  15. Anurag Singh says:

    Hi Debra,

    Great Blog!! In Example 03 how were you able to highlight the value in yellow for the returned value??

Leave a Reply

Your email address will not be published. Required fields are marked *