Choosing a column value based on a date

[praveen23]

Hi, I am trying to figure out an employee pay amount based on date of hire. If the employee has been here for less than 5 years, I want to use the value in one column for their pay. If the employee has been here for longer than 5 years but less than 7 years, I want to use a different column value, and greater than 7 years a third column value, similar to below. Is there a way to do this?

DATE Hire -- $ <5 -- $ 5-7 -- $ >7 -- $ to pay
1/2/2019 -- 10 -- 15 -- 20 -- ?

 

[praveen23]

Any help would be appreciated, I haven't been able to get anywhere with this!

 

[praveen23]

Start Date	5/31/2021​	<5 yrs	5-7 yrs	>7 yrs	Step Rate'
9/6/2011	9​	10​	15.00	20.00	20​
6/25/2000	20​	10​	15.00	20.00	20​
6/25/2000	20​	10​	15.00	20.00	20​
6/25/2004	16​	10​	15.00	20.00	20​
9/9/2019	1​	10​	15.00	20.00	10​
11/7/2016	4​	10​	15.00	20.00	10​
6/25/2004	16​	10​	15.00	20.00	20​
6/25/2015	5​	10​	15.00	20.00	15​
6/25/2012	8​	10​	15.00	20.00	20​
8/13/2012	8​	10​	15.00	20.00	20​

 

[jtakw]

Hi,

2 ways, I'm using your B column in the formulas:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Start Date	5/31/2021	<5 yrs	5-7 yrs	>7 yrs	Step Rate'			Lookup Table
2	9/6/2011	9	10	15	20	20	20		0	10
3	6/25/2000	20	10	15	20	20	20		5	15
4	6/25/2000	20	10	15	20	20	20		8	20
5	6/25/2004	16	10	15	20	20	20
6	9/9/2019	1	10	15	20	10	10
7	11/7/2016	4	10	15	20	10	10
8	6/25/2004	16	10	15	20	20	20
9	6/25/2015	5	10	15	20	15	15
10	6/25/2012	8	10	15	20	20	20
11	8/13/2012	8	10	15	20	20	20
12
13						^using table	w/o table
Sheet818

Cell Formulas
Range	Formula
F2:F11	F2	=LOOKUP(B2,I$2:I$4,J$2:J$4)
G2:G11	G2	=IF(B2<5,10,IF(B2<=7,15,20))