Annual Bonus Calc Based on Hired Month

[merivera]

Hi-

I need help trying to come up with a formula that will retrieve a set bonus percent based on hired month. I'm working on 2019. Please see my data below.

Month Pro-Rate
Jan 19 100%
Feb 19 92%
Mar 19 83%
Apr 19 75%
May 19 67%
Jun 19 58%
Jul 19 50%
Aug 19 42%
Sep 19 33%
Oct 19 0%
Nov 19 0%
Dec 19 0%

Anyone that was hired prior to 1/1/19 qualify for 100% of their bonus. Anyone hired as of 10/1/19 through current date does not qualify. Here's my list of test employees.

EE Name Date of hire
Jon Jon 01/01/2015
Jane Doe 01/01/2014
John Doe 01/06/2015
Sam Mitchell 01/01/2019
Grant Hill 11/01/2019
Lucas, Ken C 01/01/2020

If you started prior to 1/31/2019 your bonus amount would be 100%.

If you started on 2/1/2019 through current date, your bonus would be prorated.

Help I need to solve this asap.

Thank you!

 

[Dave Patton]

Book1
	A	B	C	D	E	F
1	Name	Hire Date	Bonus %		01-Jan-1925	100%
2	Jon Jon	01-Jan-15	100.00%		01-Feb-19	92%
3	Jane Doe	01-Jan-14	100.00%		01-Mar-19	83%
4	John Doe	01-Jun-15	100.00%		01-Apr-19	75%
5	Sam Mitchell	01-Jan-19	100.00%		01-May-19	67%
6	Grant Hill	01-Nov-19	0.00%		01-Jun-19	58%
7	Ken Lucas	01-Jan-20	0.00%		01-Jul-19	50%
8	a	01-Apr-19	75.00%		01-Aug-19	42%
9	b	01-May-19	67.00%		01-Sep-19	33%
10	c	01-Jun-19	58.00%		01-Oct-19
11	d	01-Feb-19	92.00%
1e

Cell Formulas
Range	Formula
C2:C11	C2	=LOOKUP(B2,$E$1:$F$10)

If you do not want the Lookup table

=LOOKUP(B2,{9133,1;43497,0.92;43525,0.83;43556,0.75;43586,0.67;43617,0.58;43647,0.5;43678,0.42;43709,0.33;43739,0})

 

[merivera]

This formula worked! Thank you.

If I wanted to house the table on a seperate tab so I could have the flexibility to adjust the percents in the future. What would the formula be?

 

[Dave Patton]

With the table on a sheet named 1d formula would be =LOOKUP(B2,'1d'!$E$1:$F$10)

Edit for the name of the Sheet that you decide to use.

An alternative would be to name the Lookup information in Formulas Name Manager for example
- new name say Bonus
={9133,1;43497,0.92;43525,0.83;43556,0.75;43586,0.67;43617,0.58;43647,0.5;43678,0.42;43709,0.33;43739,0}

The formula would then be =LOOKUP(B3,Bonus)