Looking up rewards from table

Jaypee666

New Member
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
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
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
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
+Fluff 1.xlsm
ABCDEFGHIJKLM
1Current StatsRankCurrent LevelTo Next LevelNameBronze:Silver:Gold:Platinum:Onyx:
2Agent NameTom Sales (\$)Explorer100002500050000100000500000
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

+Fluff 1.xlsm
ABCDEFGHIJKLM
1Current StatsRankCurrent LevelTo Next LevelNameBronze:Silver:Gold:Platinum:Onyx:
2Agent NameTom Sales (\$)Explorer100002500050000100000500000
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
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
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
You're welcome & thanks for the feedback.

Replies
1
Views
228
Replies
5
Views
210
Replies
1
Views
145
Replies
3
Views
108
Replies
23
Views
3K

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.

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

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