+ Reply to Thread
Results 1 to 5 of 5

Help required on INDEX function

  1. #1
    Registered User
    Join Date
    06-23-2013
    Location
    Newcastle upon Tyne, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Help required on INDEX function

    Hi,

    I'm sure this is really simple but I'm struggling. Basically I'm trying to return a value when a figure is within a certain range. I searched the internet and found a semi solution although it doesn't quite work. I think I'm on the right track but just need some help to finish it correctly. See below, I'm entering a value and expecting to see a certain time but it's not quite right:

    =INDEX(C2:C13,SUMPRODUCT(--($F$5<=B2:B13),--($F$5>=A2:A13),ROW(C2:C13)))

    Range 1 Range 2 Time
    51 150 00:11
    151 200 00:11
    201 250 00:11
    251 300 00:12
    301 350 00:12
    351 400 00:12
    401 450 00:13
    451 500 00:13
    501 550 00:14
    551 600 00:15
    601 650 00:16
    651 700 00:16

    I hope I've explained this well enough, any help would be greatly received.

    Thanks

    Andy

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help required on INDEX function

    With your table in the range D2:F13...

    A2 = some value

    =LOOKUP(A2,D2:F13)

    You can reduce the table to:

    51........0:11
    251......0:12
    401......0:13
    501......0:14
    551......0:15
    601......0:16
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Help required on INDEX function

    you could probably just use
    =LOOKUP($F$5,$A$2:$A$13,$C$2:$C$13)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    06-23-2013
    Location
    Newcastle upon Tyne, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Help required on INDEX function

    Looks like I was over-complicating things!

    Many thanks for your help!

    Andy

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Help required on INDEX function

    Moderator's Note:

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1