Choosing a column value based on a date

praveen23

Board Regular
Joined
Jun 24, 2004
Messages
120
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 -- ?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

praveen23

Board Regular
Joined
Jun 24, 2004
Messages
120
Any help would be appreciated, I haven't been able to get anywhere with this!
 

praveen23

Board Regular
Joined
Jun 24, 2004
Messages
120
Start Date
5/31/2021​
<5 yrs5-7 yrs>7 yrsStep Rate'
9/6/2011
9​
10​
15.0020.00
20​
6/25/2000
20​
10​
15.0020.00
20​
6/25/2000
20​
10​
15.0020.00
20​
6/25/2004
16​
10​
15.0020.00
20​
9/9/2019
1​
10​
15.0020.00
10​
11/7/2016
4​
10​
15.0020.00
10​
6/25/2004
16​
10​
15.0020.00
20​
6/25/2015
5​
10​
15.0020.00
15​
6/25/2012
8​
10​
15.0020.00
20​
8/13/2012
8​
10​
15.0020.00
20​
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

Book3.xlsx
ABCDEFGHIJ
1Start Date5/31/2021<5 yrs5-7 yrs>7 yrsStep Rate'Lookup Table
29/6/201191015202020010
36/25/2000201015202020515
46/25/2000201015202020820
56/25/2004161015202020
69/9/201911015201010
711/7/201641015201010
86/25/2004161015202020
96/25/201551015201515
106/25/201281015202020
118/13/201281015202020
12
13^using tablew/o table
Sheet818
Cell Formulas
RangeFormula
F2:F11F2=LOOKUP(B2,I$2:I$4,J$2:J$4)
G2:G11G2=IF(B2<5,10,IF(B2<=7,15,20))
 

Forum statistics

Threads
1,143,620
Messages
5,719,784
Members
422,244
Latest member
AYSHANA

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