# Vacation accrual formula help please

#### rrmando

##### Board Regular
Hello all. We are using the following vacation accrual table (the 1st row is service months, the rest of the data is vacation accrual days, i.e. vacation flag 3 we would accrue 10 days for 28 months of employment):

Code:
``````Vacation 	Service Years
Flag	12	24	36	48	>49	>85
3	    5	10	10	10	15	15
1	    0	 0	0	 0	 0	 0
2	    5	10	10	10	15	15
20	   0	0	 0	 0	 0	 0
17	   5	5	 10	10	10	15
15	   5	5	 10	10	10	15``````

I was hoping to use INDEX/MATCH to bring back the appropriate value based on the vacation flag and number of months of each employee (i.e. column a = name, column b = vacation flag, column c = # of months employed, column d = # of days to accrue based on the accrual table), but I cannot figure it out. I am getting confused because the number of service months will be all over the place. I was trying to adapt this formula (generously provided on this forum) but it's way above my head:

=SUMPRODUCT(--(H6>A\$3:A\$6),H6-A\$3:A\$6,INDEX(B\$3:C\$6,0,MATCH(E6,B\$1:C\$1,0))-INDEX(B\$2:C\$5,0,MATCH(E6,B\$1:C\$1,0)))

The references in the formula above are not the same as the current table we are using of course. I was just trying to figure out if something like this would work for our current accrual. Any help will be greatly appreciated. Thank you for your time.

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### acw

##### MrExcel MVP
Hi

Assume your table is on sheet 2 in the range A1:H8. Change the structure slightly so that:
1) you have an extra column of data before the 12 moths that is all 0
2) >49 becomes 49
3) >85 becomes 86

The second line will then be
Flag,0,12,24,36,48,49,86

With your data in sheet1!A:C with headings in row 1 as described above try the formula

=INDEX(Sheet2!\$B\$3:\$H\$8,MATCH(Sheet1!B2,Sheet2!\$A\$3:\$A\$8,0),MATCH(C2,Sheet2!\$B\$2:\$H\$2,1))

in D2. Copy down as required.

Tony

#### rrmando

##### Board Regular
Thank you so much for the reply Tony. This works great. I did not know you could use multiple MATCH with INDEX. Is there a limit to the number of MATCH you can use? Thanks again for your help.

#### acw

##### MrExcel MVP
I dare say there is, but it is probably more related to the length / complexity of the formula you create. The index function only uses 2 direction parameters (row and column) so using a match to determine these parameters is a reasonably common method.

Tony

Replies
7
Views
964
Replies
1
Views
686
Replies
1
Views
412
Replies
6
Views
760
Replies
0
Views
185

1,136,266
Messages
5,674,728
Members
419,523
Latest member
Urnovio

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