if statement adn formula

[chris007]

Hello again people, Newby Here agin needing some kind asitance if possible.

I ma trying to put together a formula that will auto poulate a field.

criteria is as follows: In cell CE3 i am trying to have the text located in the worksheet 'Look up table' placed in cell CE3 depending on the value in CD3.

If(CD3>=300,'look up table'!M10,If(CD3 is between 91 to 300,'look up table'!M11,If(CD3 is between 31 to 90,'look up table'!M12,IF(CD3 is between 11 to 30,'look up table'!M13,IF(CD3<30,'look up table'!M14,If(CD3="",0))))))
in each of the cells where the look up is text needs to be placed into CE3

can any one please assit with this for me

Thank you

 

[energman58]

If I understand correctly this will work - I have assumed that if CD3 is less than or equal to 11 then you want M14. You should also check that the > in my version should not be >= depending on which value you want to return in each case.

=If(CD3="",0,If(CD3>=300,'look up table'!M10,If(CD3>91,'look up table'!M11,If(CD3 >31,'look up table'!M12,IF(CD3 >11,'look up table'!M13,'look up table'!M14)))))

 

[chris007]

energman58,
thanks, that works fine and your assumption is correct.
interesting when I tried the formula with just a single lokup it returned a "False Statement that is why I did not continue on but your resolution has worked perfectly.

thank you agin for your time and helping a newby to the site..regards
Chris007

 

[energman58]

You are welcome

 

[chris007]

Sorry,
one small issue when CD3 is contains 0 how can i get CE3 to display a baln cell.

At present it displays the text for below 11 as the formula correctly states to do

thank you

 

[energman58]

=If(CD3="",0,If(CD3>=300,'look up table'!M10,If(CD3>91,'look up table'!M11,If(CD3 >31,'look up table'!M12,IF(CD3 >11,'look up table'!M13,if(CD3=0,"",'look up table'!M14))))))