Looking up rewards from table

Jaypee666

New Member
Joined
May 26, 2020
Messages
16
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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
ABCDEFGHIJKLM
1Current StatsRankCurrent LevelTo Next LevelNameBronze:Silver:Gold:Platinum:Onyx:
2Agent NameTom Sales ($)Explorer100002500050000100000500000
3 Leads GeneratedTrecker100150300450600
4Sales ($)32500ExplorerSilver17500Leads ClosedBuilder5075150225300
5Leads Closed103BuilderBronze47
6Leads Generated48Trecker2
7 
Medals
Cell Formulas
RangeFormula
C2:C7C2=IFNA(INDEX($H$2:$H$23,MATCH(A2,$G$2:$G$23,0)),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A51:A1048576,A1Cell ValueduplicatestextNO


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 :)
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,424
Office Version
  1. 2019
Surely D5 should be Silver?

Leads Closed / Builder for Tom is 103

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

Jaypee666

New Member
Joined
May 26, 2020
Messages
16
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,943
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDEFGHIJKLM
1Current StatsRankCurrent LevelTo Next LevelNameBronze:Silver:Gold:Platinum:Onyx:
2Agent NameTom Sales ($)Explorer100002500050000100000500000
3 Leads GeneratedTrecker100150300450600
4Sales ($)32500ExplorerSilver:17500Leads ClosedBuilder5075150225300
5Leads Closed103BuilderSilver:47
6Leads Generated48Trecker 52
Lists
Cell Formulas
RangeFormula
C2:C6C2=IFNA(INDEX($H$2:$H$23,MATCH(A2,$G$2:$G$23,0)),"")
D4:D6D4=IFNA(INDEX($I$1:$M$1,MATCH(B4,INDEX($I$2:$M$4,MATCH(A4,$G$2:$G$4,0),),1)),"")
E4:E6E4=INDEX($I$2:$M$4,MATCH(A4,$G$2:$G$4,0),IFNA(MATCH(D4,$I$1:$M$1,0)+1,1))-B4
 

Jaypee666

New Member
Joined
May 26, 2020
Messages
16
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows

ADVERTISEMENT

How about
+Fluff 1.xlsm
ABCDEFGHIJKLM
1Current StatsRankCurrent LevelTo Next LevelNameBronze:Silver:Gold:Platinum:Onyx:
2Agent NameTom Sales ($)Explorer100002500050000100000500000
3 Leads GeneratedTrecker100150300450600
4Sales ($)32500ExplorerSilver:17500Leads ClosedBuilder5075150225300
5Leads Closed103BuilderSilver:47
6Leads Generated48Trecker 52
Lists
Cell Formulas
RangeFormula
C2:C6C2=IFNA(INDEX($H$2:$H$23,MATCH(A2,$G$2:$G$23,0)),"")
D4:D6D4=IFNA(INDEX($I$1:$M$1,MATCH(B4,INDEX($I$2:$M$4,MATCH(A4,$G$2:$G$4,0),),1)),"")
E4:E6E4=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,943
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=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

New Member
Joined
May 26, 2020
Messages
16
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
How about
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,943
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,148,195
Messages
5,745,285
Members
423,942
Latest member
excelhelp1423

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top