excel if and lookup

kenc

New Member
Joined
Jul 25, 2006
Messages
10
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Try:

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

kenc

New Member
Joined
Jul 25, 2006
Messages
10
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
=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

New Member
Joined
Jul 25, 2006
Messages
10

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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

New Member
Joined
Jul 25, 2006
Messages
10
Thanks alot for your help, so I would just continue to add on until all the criteria was in the min formula?
 

kenc

New Member
Joined
Jul 25, 2006
Messages
10
Sorry, I did not read it to close, everything is there. Thanks again.
 

Forum statistics

Threads
1,137,298
Messages
5,680,678
Members
419,924
Latest member
Dhamodharan992

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top