This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
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 | ?? |
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
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.
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")
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.
=FILTER($C$2:$C$6, (E2>=$A$2:$A$6)*(E2<=$B$2:$B$6), "")
As a technique, I would sort the table in order to make it much easier to do them all at once.