Share via

Selecting a value that fits between two values

Anonymous
2024-11-09T20:25:52+00:00

I have a problem finding a correct formula to solve my problem.

Basically I have in one table START and END range number and then the range name. In the second table I have just values and I want to know which exact range NAME will belong to that value. Those ranges that I have are not continues ranges (1-5, 6-15, 16-30, etc.) so I can't use LOOKUP value that checks basically only start range column. About an year ago I managed to find the formula, but not any more.

How to do it? See example from the following table, that is illustrative.

Range START Range END Range NAME VALUE Corresponding range name
569 597 AAA 266 ??
159 178 BBB 169 ??
258 297 CCC 178 ??
726 842 DDD 399 ??
358 415 EEE 577 ??
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. ppavle 5,195 Reputation points Volunteer Moderator
    2024-11-09T21:57:24+00:00

    Ok, I created the following Excel sheet based on your data

    Selected A2:A6, right click, Define Name, "RangeStart". B2:B6, name "RangeEnd", C2:C6, name "RangeName".

    Then on F2, enter formula: =IFERROR(INDEX(RangeName, MATCH(1, (E2 >= RangeStart) * (E2 <= RangeEnd), 0)), "Not Found")

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2024-11-09T22:55:12+00:00

    Hi,

    In cell F2, enter this formula

    =XLOOKUP(1,((E2>=$A$2:$A$6)*(E2<=$B$2:$B$6)),$C$2:$C$6)

    Hope this helps.

    0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2024-11-09T21:46:01+00:00

    =FILTER($C$2:$C$6, (E2>=$A$2:$A$6)*(E2<=$B$2:$B$6), "")

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-11-10T00:22:04+00:00

    As a technique, I would sort the table in order to make it much easier to do them all at once.

    0 comments