Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    INDEX function using labels (2003)

    I am trying to use the INDEX function in Excel with labels rather than numbers (please see attached). I am trying to use the labels in cells B9 and B10 (which both have Data Validation Lists) as arguments in the INDEX function - eg =INDEX(D2:E7,B9,B10). The function works fine if I type in the numbers manually - eg 2, 1 instead of B9 and B10 (see cell B14). It also works fine if I use cell references - eg C9 and C10 where the cells contain numbers (see cell B13). Is it possible to use the labels in B9 and B10 in the INDEX function? Or is there a different formula which will do the job for me? I can get round the problem by using Combo Box Controls from the Forms toolbar, but would rather use Validation Lists if possible. Any ideas would be gratefully accepted. Thanks in advance for any help you are able to give.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INDEX function using labels (2003)

    You can incorporate the Match() function to achieve what you are attempting to do.

    Formula in B12:

    =INDEX(D2:E7,MATCH(B9,C2:C7,0),MATCH(B10,D1:E1,0))

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INDEX function using labels (2003)

    You can use MATCH to look up the index values:

    =INDEX(D2:E7,MATCH(B9,C2:C7,0),MATCH(B10,D1:E1,0))

  4. #4
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INDEX function using labels (2003)

    Perfect! Thank you so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •