Looking up rewards from table

[Jaypee666]

Hi All,

I'm sure this is has probably been answered somewhere already, I'm just not able to word my query correctly.

I have a Worksheet with a list of Sales Agents.
On the Sheet are a number of different Reward Levels for different aspects of their job, Sales, Leads Generated, Leads Closed, etc.
At the moment I have to up date the sheet manually on a weekly basis and was hoping that I could find a way to "automate it a bit, so that when I enter in the relevant stats for the week, that the sheet would do the ranking so as to ensure that firstly, a Sales Agent isn't mistakenly on a lower rank than they deserve due to a miscalculation and also, that someone isn't on a Hight Rank, if not earned yet.

Below is a demo of what I have at the moment:

RankingDemo.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Current Stats	Rank	Current Level	To Next Level			Name	Bronze:	Silver:	Gold:	Platinum:	Onyx:
2	Agent Name	Tom					Sales ($)	Explorer	10000	25000	50000	100000	500000
3							Leads Generated	Trecker	100	150	300	450	600
4	Sales ($)	32500	Explorer	Silver	17500		Leads Closed	Builder	50	75	150	225	300
5	Leads Closed	103	Builder	Bronze	47
6	Leads Generated	48	Trecker		2
7
Medals

Cell Formulas
Range	Formula
C2:C7	C2	=IFNA(INDEX($H$2:$H$23,MATCH(A2,$G$2:$G$23,0)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A51:A1048576,A1	Cell Value	duplicates	text	NO

Currently the following formula is in Column C:
=IFNA(INDEX($H$2:$H$23,MATCH(A4,$G$2:$G$23,0)),"")

Column A will have a number of Categories tracking an Agents progress, but not all will have Reward levels.

What I want to try and automate is Column D & E.
In Column D I want to have it pull in the Current Level the agent is on based on What is returned in Column C in relation to the Data entered in Column B, eg if B4 is between 50000 & 99999 then C4 = Gold and E4 would perform the calculation required to reach Platinum at 100000.

Any and all help greatly accepted, even if it's just some tips on how to word my search

 

[Special-K99]

Surely D5 should be Silver?

Leads Closed / Builder for Tom is 103

Leads Closed / Builder minimum is 75, Tom has surpassed this value.

 

[Jaypee666]

Surely D5 should be Silver?

Leads Closed / Builder for Tom is 103

Leads Closed / Builder minimum is 75, Tom has surpassed this value.

Well spotted on Builder

D5 is an example of a mistake that could go either way.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Current Stats	Rank	Current Level	To Next Level			Name	Bronze:	Silver:	Gold:	Platinum:	Onyx:
2	Agent Name	Tom					Sales ($)	Explorer	10000	25000	50000	100000	500000
3							Leads Generated	Trecker	100	150	300	450	600
4	Sales ($)	32500	Explorer	Silver:	17500		Leads Closed	Builder	50	75	150	225	300
5	Leads Closed	103	Builder	Silver:	47
6	Leads Generated	48	Trecker		52
Lists

Cell Formulas
Range	Formula
C2:C6	C2	=IFNA(INDEX($H$2:$H$23,MATCH(A2,$G$2:$G$23,0)),"")
D4:D6	D4	=IFNA(INDEX($I$1:$M$1,MATCH(B4,INDEX($I$2:$M$4,MATCH(A4,$G$2:$G$4,0),),1)),"")
E4:E6	E4	=INDEX($I$2:$M$4,MATCH(A4,$G$2:$G$4,0),IFNA(MATCH(D4,$I$1:$M$1,0)+1,1))-B4

 

[Jaypee666]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Current Stats	Rank	Current Level	To Next Level			Name	Bronze:	Silver:	Gold:	Platinum:	Onyx:
2	Agent Name	Tom					Sales ($)	Explorer	10000	25000	50000	100000	500000
3							Leads Generated	Trecker	100	150	300	450	600
4	Sales ($)	32500	Explorer	Silver:	17500		Leads Closed	Builder	50	75	150	225	300
5	Leads Closed	103	Builder	Silver:	47
6	Leads Generated	48	Trecker		52
Lists

Cell Formulas
Range	Formula
C2:C6	C2	=IFNA(INDEX($H$2:$H$23,MATCH(A2,$G$2:$G$23,0)),"")
D4:D6	D4	=IFNA(INDEX($I$1:$M$1,MATCH(B4,INDEX($I$2:$M$4,MATCH(A4,$G$2:$G$4,0),),1)),"")
E4:E6	E4	=INDEX($I$2:$M$4,MATCH(A4,$G$2:$G$4,0),IFNA(MATCH(D4,$I$1:$M$1,0)+1,1))-B4

Exactly what I was trying to do, but nesting has never been my strong point!! Thank you.

Quick follow up query, how do I stop a #REF when the agent hits Onyx level as there is nothing to subtract the current standing from? Or maybe how do I have it just say "Onyx"?

 

[Fluff]

How about

=MAX(0,INDEX($I$2:$N$4,MATCH(A4,$G$2:$G$4,0),IFNA(MATCH(D4,$I$1:$M$1,0)+1,1))-B4)

This will simply return 0

 

[Jaypee666]

How about

=MAX(0,INDEX($I$2:$N$4,MATCH(A4,$G$2:$G$4,0),IFNA(MATCH(D4,$I$1:$M$1,0)+1,1))-B4)

This will simply return 0

That looks like it's done the trick, than you

 

[Fluff]

You're welcome & thanks for the feedback.