Nested And/IF Statments [Solved/Closed]

Report
Posts
2
Registration date
Friday November 1, 2013
Status
Member
Last seen
November 4, 2013
-
Posts
2
Registration date
Friday November 1, 2013
Status
Member
Last seen
November 4, 2013
-
Hello and good day.

I am in the process of putting together an annual bonus/vacation accural sheet and need some assistance with building a complex function. Here is what is needing to occur.

In cell A1 is the number of years that a mariner has been employed
In cell A2, I need it to reference cell A1 and return a value of either 10 days, 15 days or 22 days based upon the below information.

1-4 years = 10
5-9 years = 15
10+ years = 22

Can this be done? I am attempting to do this with in 2013 and when I go to piece meal all the data, my system screams error.

Help???

2 replies

Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
Hi nelsojm

Try the following LOOKUP function.

=LOOKUP(A1,{0,5,10},{10,15,22})

Or another alternative is the VLOOKUP.

https://www.contextures.com/xlFunctions02.html
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2
Registration date
Friday November 1, 2013
Status
Member
Last seen
November 4, 2013

Thank you so much. This was much easier to use than IF statements.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
try this
=IF(a1="","", if(a1>=10,22,if(a1>=5,15, if(a1>=1,10,0))))