• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Best way to Buckets (like using lookup)

xlstime

Active Member
Dear All,

Is there any way to lookup values which have already in range like 90-95, 80-85 so on, without making helper column.

Using table one i required bucket value in table two. (I am able to do this using lookup function but using helper column).

Please suggest the another way to do this without using helper colum


table one -

Mapping Buckets
90+ A
85-90 B
80-85 C
50-80 D
30-50 E
20-30 F
>20 G

table two-


Values Buckets
51
85
38
88
72
49
41
91
51
90
97
 
The way you have simplified it in table 3 is better approach from maintainability perspective. Here's one that will work straight with table 2.
=LOOKUP(2,1/FREQUENCY(-B4,-LEFT(SUBSTITUTE($G$4:$G$10,">","00"),2)),$H$4:$H$10)
I am sure there'd be better & elegant way of doing it.
 
Gajab shrivallabha,

This is what I was looking.

Thanks


The way you have simplified it in table 3 is better approach from maintainability perspective. Here's one that will work straight with table 2.
=LOOKUP(2,1/FREQUENCY(-B4,-LEFT(SUBSTITUTE($G$4:$G$10,">","00"),2)),$H$4:$H$10)
I am sure there'd be better & elegant way of doing it.
 
Back
Top