Designation & % of Achievement

[muralli]

Hi friends,

I have a Sales Incentive Program that payouts to staff based on their designation and % achieved. Below is the criteria table.

Designation	Pay-1 90%	Pay-2 100%	Pay-3 110%	Pay-4 120%
Sales Associate	60	100	150	300
Watches Technician	60	100	150	300
Senior Sales Associate	60	100	150	300
Visual Merchandiser	75	150	225	450
Assistant Shop Manager	75	150	225	450
Shop Manager	75	175	265	525

<tbody>
</tbody>

Scenario-1: Shop target is 10,000; sales achieved is 9,500; achievement is 95%; hence payout will be based on 'Pay-1'
Scenario-2: Shop target is 10,000; sales achieved is 11,500; achievement is 115%; hence payout will be based on 'Pay-3'
Scenario-3: Shop target is 10,000; sales achieved is 14,000; achievement is 140%; hence payout will be based on 'Pay-4'

If the 90% achievement threshold is not achieved, no incentive will be paid out. No rounding off % achieved is allowed. Policy is strict for payout.

How do I write a formula or array formula for this situation?

Please help.

Thank you.

Regards
Muralli Srinivasan

 

[AlanY]

one way

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	Designation	Pay-1	Pay-2	Pay-3	Pay-4		target	achieved
2		90%	100%	110%	120%
3	Sales Associate	60	100	150	300		10000	9500	95%	Pay-1
4	Watches Technician	60	100	150	300		10000	11500	115%	Pay-3
5	Senior Sales Associate	60	100	150	300		10000	14000	140%	Pay-4
6	Visual Merchandiser	75	150	225	450
7	Assistant Shop Manager	75	150	225	450
8	Shop Manager	75	175	265	525
Sheet3

Cell Formulas
Range	Formula
J3	=LOOKUP(I3,$B$2:$E$2,$B$1:$E$1)

 

[muralli]

Thank you Alan. Though I was looking for the full solution (payout amount), you gave me the right lead. I have solved the issue.

I used a combination of Index-Match-Lookup formula for the final formula and it worked.

Amazing platform this one...

one way

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J
1	Designation	Pay-1	Pay-2	Pay-3	Pay-4		target	achieved
2		90%	100%	110%	120%
3	Sales Associate	60	100	150	300		10000	9500	95%	Pay-1
4	Watches Technician	60	100	150	300		10000	11500	115%	Pay-3
5	Senior Sales Associate	60	100	150	300		10000	14000	140%	Pay-4
6	Visual Merchandiser	75	150	225	450
7	Assistant Shop Manager	75	150	225	450
8	Shop Manager	75	175	265	525

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula J3 =LOOKUP(I3,$B$2:$E$2,$B$1:$E$1) <tbody> </tbody>

<tbody>
</tbody>

 

[AlanY]

you're welcome