Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    VLOOKUP round up (2000 SR2)

    Hi, I have a problem with the VLOOKUP function. I'd like it to find the next value up, rather than down, attached file explains this in more detail, any ideas?

    thanks,
    Jim MacLeod
    Shetland Isles

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VLOOKUP round up (2000 SR2)

    Does this work?

    =IF(J4<L4,VLOOKUP(K4,Grades,2,FALSE),IF(J4>M4,VLOO KUP(K4,Grades,3,FALSE),INDEX(GradePoints,MATCH(J4, INDEX(GradePoints,0,1))+1,1)))

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: VLOOKUP round up (2000 SR2)

    Yes it does, thanks very much.

    I have to admit that the logic of INDEX(GradePoints,0,1))+1 escapes me though. Does it simply take F4:F22 and add 1 to give F5:F23?

    Jim

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VLOOKUP round up (2000 SR2)

    Not exactly, the last portion is:

    INDEX(GradePoints,MATCH(J4,INDEX(GradePoints,0,1)) +1,1)))

    INDEX(GradePoints,0,1)
    Creates an array all the rows in range GradePoints, but only the first column. (Lookups can work with multiple columns, MATCH requires only 1 column). [Note: you could create a named range comprising of just column 1 and not use "INDEX(GradePoints,0,1)"]

    MATCH(J4,INDEX(GradePoints,0,1))
    Looks for the value of J4 in the first column of GradePoints and gives the highest value that is less than or equal to it.

    INDEX(GradePoints,MATCH(J4,INDEX(GradePoints,0,1)) +1,1)))
    This looks in GradePoints at the row 1 more than the "matched" value, in the first column.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: VLOOKUP round up (2000 SR2)

    Ahhhhh! ......sound of pennies dropping and lightbulbs coming on!

    Thanks for the explanation,
    Jim

Posting Permissions

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