Calculating vacation entitlement

[kmlloyd]

I use Excel 2003 and our vacation entitlement is based on a number of factors. I am trying to create a formula that will pull out an employee's vacation entitlement based on their vacation code and years of service, but there are too many nested if's. Attached is the vacation matrix to show what the entitlement is as well as a sample of the report.

The figures in yellow are the figures I want the report to pull under the "Entitlement" column E. Column F shows what the entitlement should be based on the rules.

Thanks in advance!


ETA: I figured out an Index/Match that will work but the only way I know how to make it work is by using a huge chart listing the years of service from 0-40)...is there a better way to use ranges?

[NBVC]

Change the values in C2 to F2 to the lower boundaries of each range... ie. 0, 5, 10, 20 and then use formula:

=INDEX($C$3:$F$12,MATCH(D16,$B$3:$B$12,0),MATCH(C16,$C$2:$F$2))

copied down.

[kmlloyd]

Awesome Thank You!!