Results 1 to 4 of 4

Thread: How to get in which range a value lies out of multiple ranges
Thanks Thanks: 0 Likes Likes: 0

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

    Default How to get in which range a value lies out of multiple ranges

    I have a value e.g 48
    There are multiple ranges, and each range will correspond to a specific amount, example below:
    11 to 20 --> 15
    21 to 30 --> 25
    31 to 40 --> 35
    41 to 50 --> 45

    I want to search for the value 45, so i need to check the multiple ranges to get that specific value on the right-hand side.
    So if my value is 48, i want to get 45, likewise if the value is 12 then i want to get back 15.

    Is that possible in excel?
    Thank you!

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,645
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: How to get in which range a value lies out of multiple ranges

    If you put this in A1:C4

    11 20 15
    21 30 25
    31 40 35
    41 50 45

    Then either
    =LOOKUP(48, A1:A4, C1:C4)
    or
    =VLOOKUP(48, A1:C4, 3)

    will return what you want

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get in which range a value lies out of multiple ranges

    Quote Originally Posted by mikerickson View Post
    If you put this in A1:C4

    11 20 15
    21 30 25
    31 40 35
    41 50 45

    Then either
    =LOOKUP(48, A1:A4, C1:C4)
    or
    =VLOOKUP(48, A1:C4, 3)

    will return what you want
    Thank you, how about this:
    What if i have the ranges within the same cells as specified?
    11 - 20 15
    21 - 30 25
    31 - 40 35

    The range themselves should be in the same cell separated by a dash (-), but the value to retrieve will still be in a cell of its own.
    Is that doable?

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,645
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: How to get in which range a value lies out of multiple ranges

    Its doable with VBA.
    If you don't know VBA, the formula approach (with one piece of data per cell) is the only way.

Some videos you may like

User Tag List

Tags for this Thread

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
  •