Thread: lookup or index? > issue

Hello

Having some trouble trying to place criterias on my data.

I need two things

a) using sales ppl tenure (in months), i need to bucket them thru lookup (cant figure out how), to place them into certain "buckets" i.e. 0-3 months, 4-6 months, 7-9 months etc.

for example, John Doe tenure 4.56 months, therefore, in 4-6month bucket (I have highlighted this portion in yellow) on the worksheet

b) using the same concept, i need to now "bucket" their sales performance % into buckets i.e. less than 50%, 50-74.99%

again, i dont need the sumproduct, i just need a lookup or if statement for it to say, what bracket it belongs in

for ex.

in E14
I want it to say: 4-6

F14: would lookup value in J14 and state:
50%-74.99%

can someone pls help me with this, been struggling wid an easy issue, pls and thx you!

Where does a number like 3.43 fall? It isn't 0-3 or 4-6.

in column 14-L20 next to your list place in ascending order 0
3
6
9
12
24

then use the Vlookup in E14 and drag down VLOOKUP(D14,$L$14:$M$19,2)

Well your bucket tables are organized a bit different....

In E14, try:

=LOOKUP(D14,LEFT($M$14:$M$19,FIND("-",$M$14:$M$19&"-")-1)+0,$M$14:$M$19)

copied down

If you complete column O table by placing a 0 in O14, an 100% in O19 and 100.01% in O20, then you can use:

=LOOKUP(J14,$O$14:$O$20) in F14 copied down.

If you need the upper bounds you will need to concatenate them in your formula.

if your not able to make your bucket a table, hardcode the table into the same vlookup
=VLOOKUP(D14,{0,"0-3";3,"4-6";6,"7-9";9,"10-12";12,"13-24";24,25},2)