Formula or Vlookup in Excel

[dianemarie]

Hi, I have to return # of days vacation based on a column that has years tenure. (Tenure is already populated with the number of years an employee has.) What's the quickest way to get this done? Thank you for your help.

Tenure Column Should return days in separate column
0-3 years = 10 days
4-7 years = 15 days
8-14 years 21 days
etc.

So 1 year = 10 days
2 years = 10 days
3 years = 10 days
4 years = 15 days
etc.

 

[Zathras]

You could use either VLOOKUP or nested IF statements. I would use VLOOKUP mainly because of the easier documentation of how the parameters are set up.

For example, put these values in A1:B4
[tt]
0 10
4 15
8 21
15 28
[/tt]
Then put the number of years in A7 and this formula in B7:
[tt]
=VLOOKUP(A7,$A$1:$B$4,2,1)
[/tt]

 

[AnotherHiggins]

VLookup with the last argument set to TRUE (or 1). that will look for the nearest match that is not greater than the search term.

Example:
[tt]
A B C D
1 0 10 3 =VLOOKUP(C1,A1:B3,2,1)

2 4 15

3 8 21[/tt]


D1 will display 10.

[blue]-John[/blue]

Help us help you. Please read FAQ181-2886 before posting.

 

[dianemarie]

Thanks to both of you. The VLOOKUP was the way to go. I had too many nested arguments to go with a nested if.

 

[mike1955]

using lookup with ranges or named ranges must have the index field sorted to work correctly.

mike