Best way to Buckets (like using lookup)

[xlstime]

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

 

[Hui]

Assuming your table starts in A2:
=INDEX({"G";"F";"E";"D";"C";"B";"A"},MATCH(A2,{0;20;30;50;80;85;90},1))

 

[bosco_yip]

Another possible...

=MID("GFEDCBA",MATCH(A2,{0,20,30,50,80,85,90}),1)

Regards

 

[Khalid NGO]

Hi,

One more:

=CHOOSE(MATCH(A2,{0,20,30,50,80,85,90}),"G","F","E","D","C","B","A")

Regards,

 

[xlstime]

Thanks Khalid NGO ,bosco_yip (ExtraOrdinary) and Hui,

But, I am looking for Dynamic formula based on table 2.

Attaching the sample data with requirement.

 

[shrivallabha]

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.

 

[NARAYANK991]

Hi ,

Going by the data , I think the > symbol should be the < one.

Narayan

 

[xlstime]

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.

 

[Hui]

Table 3 is the easiest way to go
=VLOOKUP(B4,$K$4:$L$10,2)