Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Data Lookup

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have used the VLOOKUP command but cannot get my desired output. When I type in the "input value", the result is the largest value in my lookup table that is smaller that the "input value". This is how it should work. But, I need for the result to return the next largest value in the lookup table. Any suggestions?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-25 13:00, CHAS wrote:
    I have used the VLOOKUP command but cannot get my desired output. When I type in the "input value", the result is the largest value in my lookup table that is smaller that the "input value". This is how it should work. But, I need for the result to return the next largest value in the lookup table. Any suggestions?
    Care to provide a small example along with expected results?

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =OFFSET(INDEX(A1:A6,MATCH(B1,A1:A6)),1,)

    where B1 contains your lookup value. If B1 is 2 and A1:A6 contains {1;2;3;4;5;6} or {1;1.9;3;4;5;6} then this formula returns 3

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If my "input value" is 21.5, then I want the return to 25. Below is my table.


    1
    3
    6
    10
    15
    20
    25
    30
    35
    40
    45
    50
    60
    70
    80
    90
    100
    110
    125
    150
    175
    200
    225
    250
    300
    350
    400
    450
    500
    600
    601
    700
    800
    1000
    1200
    1600
    2000
    2500
    3000
    4000
    5000

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    =INDEX(A2:A100,MATCH(E1,A2:A100)+1)

    where A2:A100 houses the data (adjust to suit) and E1 an "input value".

    Aladin


    On 2002-04-25 13:20, CHAS wrote:
    If my "input value" is 21.5, then I want the return to 25. Below is my table.


    1
    3
    6
    10
    15
    20
    25
    30
    35
    40
    45
    50
    60
    70
    80
    90
    100
    110
    125
    150
    175
    200
    225
    250
    300
    350
    400
    450
    500
    600
    601
    700
    800
    1000
    1200
    1600
    2000
    2500
    3000
    4000
    5000

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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