Identify range

[Noxqss38242]

Example:

17250
21222
10001
17255

I have a thousand numbers, I have divided into "groups"

I want formula to say:

IF (a2>=17200......and if a2<=17500......THEN return "17200--17500", IF a2>=21200......and if a2<=21400.......THEN return "21200--21400"......ETC.....

I have 16 groups but if needed I think I can cut them down....what is the best way to do this?

 

[saboh12617]

Hello,

Given that you are working on XL2016, the XLOOKUP is not avaliable, so you should use the INDEX/MATCH combo like this :

Make a table of your groups lower limits in a column, sorted from smaller to larger, write next to it the corresponding desired output.

For example

17200	17200--17500
21200	21200--...

Then, use MATCH on the searched value, with last parameter = 1, looking on the first column. And Index of the match result, looking on the second column.

Something like :

=INDEX(B:B,MATCH(myval,A:A,1))

 

[Joe4]

You can also use VLOOKUP if you enter the lower bounds in your lookup table and have it sorted in increasing order, and set the last argument in the VLOOKUP function (use approximate match) to TRUE.