# Lookup based on ranges

#### Mrs. T

##### Board Regular
need to figure out a way to take a date and determine how much of an accrual should be added.

In other words, I have their hire date and once they hit 6 months of employment, they receive a certain accrual amount. They accrue this amount until they hit 1 year which is a different accrual amount. The next step is at year 2 and then again at year 5.

How do I create a formula to look at the YOS and determine the highest accrual that they should receive without making a long list of dates?

This is the schedule:
1.00 - 1.99 = 3.08
2.00 - 4.99 = 3.08
5.00 - 9.99 = 4.16

An example would be: Date of hire 06.12.04 should accrue at 3.08

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### dave3009

##### Well-known Member
Hi There

Not sure if I got you right but have a look at the 2 options below. Opt 1 references a table which is easy to edit should your 'accrue' change in the future. Opt2 looks at an array in built into the function.
Book2
ABCDE
10StartDateOpt1Opt2
11Bob01/01/20071.541.54
12Dan03/06/19994.164.16
13Ian01/03/19848.328.32
14
15
16ServiceAccrual
170.51.54
1813.08
1923.08
2054.16
21108.32
Sheet1

HTH

Dave

#### barry houdini

##### MrExcel MVP
Your accrual amounts for the second two categories are the same, is that correct? I'll assume that 1 - 1.99 should be 2.22.

With hire date in A1

=LOOKUP(DATEDIF(A1,TODAY(),"y"),{0,1,2,5},{0,2.22,3.08,4.16})

The above gives zero up to 1 year but you said there was an accrual amount for 6 months? If so you could use a revised formula like this, where accrual amount for 6 months to 1 year is 1.5

=LOOKUP(DATEDIF(A1,TODAY(),"m"),{0,6,12,24,60},{0,1.5,2.22,3.08,4.16})

Note: both formulas will still give 4.16 for 10 years and beyond

#### Mrs. T

##### Board Regular
Dave3009 & Barry houdini, thank you for both of your input.

I will be using both methods in the spreadsheets that I am going to tackle on this project...

Replies
1
Views
236
Replies
0
Views
141
Replies
3
Views
284
Replies
5
Views
346
Replies
0
Views
305

1,191,690
Messages
5,988,103
Members
440,126
Latest member
duque00

### 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.

### Which adblocker are you using?

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