# excel if and lookup

#### kenc

##### New Member
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.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Try:

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

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?

=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.

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?

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})

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

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

Replies
1
Views
68
Replies
3
Views
159
Replies
9
Views
287
Replies
2
Views
187
Replies
2
Views
424

1,203,739
Messages
6,057,075
Members
444,903
Latest member
Mavericx

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