Drop down list selection within IF formula

[beuermant]

I would like to multiply an user input number (column A) by a factor based on the selection in a drop down list (column B). Column C is the formula to do the operation and display the answer, but I also want it to just be a blank cell if nothing is input in column A or selected in column B (not a 0). The factors associated with the selection in column B are in a separate Data sheet, as they may change periodically and need to be easily updated.

First question, is there a more efficient way than nested IF statements? I used the following:

=A4*(IF(B4="O1",'Calc Data'!E3,IF(B4="O2",'Calc Data'!E4,IF(B4="O3",'Calc Data'!E5,IF(B4="O4",'Calc Data'!E6,"")))))


Second, the above works minus leaving Column C blank if no selection is made or no value is input in Column A. I get a #VALUE! error now if nothing is selected or no value in Column A. I can probably add more if statements to work it out, but I really don't want to do that and I'm sure I'm forgetting something that would make it easier.

Thanks for the help!

[Pepe Le Mokko]

This is a bit shorter

Please Login or Register  to view this content.

[beuermant]

Thanks for the reply! I'm not seeing any results from this formula, even after adding the missing comma and parenthesis...

[Pepe Le Mokko]

You're right about the parenthesis. OTOH no comma is missing that I can see

Please Login or Register  to view this content.

Perhaps post a sample sheet ? ( see yellow banner)

[beuermant]

Here is a sample sheet with the different options I've tried and presented above. Thanks!

[jeffreybrown]

It seems you could just use

=IF(F4="","",E4*VLOOKUP(F4,'Calc Data'!$C$3:$E$6,3,0))

[beuermant]

Thanks jeffreybrown, that seems to do the trick!

[jeffreybrown]

You are very welcome and thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.