Lookup and assign a grade based on performance but different tiers for different people

[thedon_1]

Hi guys

I've got a problem where I want to assign a grade based on sales performance. There's a table of grades based on tiers e.g 0-100 is a C, 101-200 is a B and anything else is an A.

I don't really know what formula to use to pull in the data from one table into another and assign the correct grade.

I've put together an example sheet so I hope it helps illustrate the problem.

It's too much for a normal vlookup due to the tiers so I need some help. In my actual data set, there could be hundreds of people i'm trackig so I can't write the tiers into an if function.



Thanks

[etaf]

create a table
0 C
101 B
201 A

and then use
=VLOOKUP(B5,reference Table ,2,TRUE)

see attached

[thedon_1]

Interesting approach. This solution means I need to create a lookup table and formula specific to each person though.

Is there not a way to use one formula to lookup a consolidated table of tiers? The formula would then be applicable to all people in Column A.

Thanks for replying.

create a table
0 C
101 B
201 A

and then use
=VLOOKUP(B5,reference Table ,2,TRUE)

see attached

[Tony Valko]

Another way to write that formula:

=LOOKUP(B5,reference Table)

[AlKey]

Maybe this

=LOOKUP(B5,{0,121,2000},{"B","A"})

[thedon_1]

Thanks for the solutions guys but I can't use those since they don't take into account the different tiers for A and B.

I really can't hard code the tiers into the formula as my final actual sheet may have hundreds of people to lookup which is why I wanted to make a table of grades and tiers, then look it up depending on the Persons name and their sales, not just the sales.

[Tony Valko]

In your sample file each person has 2 tiers.

Will there always be the same number of tiers for each person?

[thedon_1]

No it could be variable, but shouldn't be more than 4

[etaf]

This solution means I need to create a lookup table and formula specific to each person though

Should not do , if the sales tiers are the same for everyone

do you have different ties for different people
if so you could have a matrix reference table

so down column A have the names and along row 1 have all the tiers
now we can look up the names and what tier Grad applies

If you want different values for different people then
you need to put all the tier cutoff values in the ROWs
and then if you have

0 100 200 300 400

and say for Person A
they are
A for upto 100 and B for upto 200 and C for anything else
you put
A B C C C
now if you person B
A for upto 100
B upto 300
C anything else

A B B C C

see example

[Tony Valko]

Try this...


Data Range

	A	B	C	D	E	F	G
4	Person	Sales	Grade	-----	Company	Minimum	Grade
5	A	80	B		A	0	B
6	B	141	A		A	101	A
7	A	120	A		B	0	B
8	B	133	A		B	121	A
9	C	57	A		C	0	C
10	C	21	B		C	20	B
11	D	199	B		C	50	A
12					D	0	B
13					D	200	A

This formula entered in C5 and copied down:

=LOOKUP(B5,OFFSET(E$5,MATCH(A5,E$5:E$13,0)-1,1,COUNTIF(E$5:E$13,A5),2))