excel if and lookup

[kenc]

I need to look at a certain cell, if that cell is between 0-4, then it would return a "1", if the cell is between 5-9, then level "2", if between 10-14 then level "3", and if between 15-17 level "4". What I am looking to do is create a formula so that I can look at a cell and if the number falls into one of these catagories then multiply the number of days by a certain figure and depending on which catagory it falls into not have it return a value over a certain amount.

 

[Oaktree]

Try:

=LOOKUP(A1,{0,1;5,2;10,3;15,4;18,"N/A"})

 

[kenc]

Thanks, that helped, how would I tackle the next thing which is to take the age group and take how many days they are here (say cel b8) and multiply it by 1.666, but they can not exceed say $25 if level1, $30 if level 2, $35 if level 3 and $50 if level 4?

 

[Oaktree]

=MIN(B8*1.666,LOOKUP(A1,{0,25;5,30;10,35;15,50;18,"N/A"}))

Where B8 has the number of days and A1 is the "certain cell" in your first post as before.

 

[kenc]

Thanks for all your help, one more step, if they are level 1 (0-4) then b8 would need to be multiplied by .83, level 2 would be by 1, level 3 would be 1.17 and level 4 would be 1.666?

 

[Oaktree]

Same idea...

=MIN(B8*LOOKUP(A1,{0,0.83;5,1;10,1.17;15,1.666;18,"N/A"}),LOOKUP(A1,{0,25;5,30;10,35;15,50;18,"N/A"}))

The syntax for the LOOKUP function in that formula is:
=LOOKUP(what to look up, {lower bound for first group, value to return for first group; lower bound for second group, value to return for second group; ...; lower bound for last group, value to return for last group})

 

[kenc]

Thanks alot for your help, so I would just continue to add on until all the criteria was in the min formula?

 

[kenc]

Sorry, I did not read it to close, everything is there. Thanks again.