Having issues with If statement

Hey all, I'm trying to write a formula for work and I'm at a stand still. What I'm wanting to do is be able to select from a drop down box (already got that going) in cell c2 and based on what option is chosen, either display a number in e2 OR figure an equation in box d2 and display that in e2. I know it's kind of confusing. So an example would be if "Checking Account", "Savings Account", "CD", or "Credit Card" are selected in cell C2, then I want "25" displayed in cell E2. If "Auto Loan" or "Mortgage" is selected from the drop down (C2) then I want it to do the formula "=IF(D2*0.05>150,"150",D2*0.05)" (there will be a number in D2 if auto loan or mortgage is selected) in E2.

=IF((LOOKUP(C2,Sheet2!B2:B5,3)),"25",(IF(D2*0.05>150,"150",D2*0.05)))

This is the formula I have now and it's only half working. When "Checking Account" and "Credit Card" are selected, the "25" pops up but not the other two. Also it doesn't do the equation in D2. Also, while all of this is happening I have a running total going on at the bottom so when it says "#N/A" it screws up the total and won't add.


Please help! I'm in a little bit over my head with this one and I cannot figure out how to make this work. Thank you in advance for any assistance!

Please post a sample file showing before & after results.

No go :/

It does the same thing

bhenlee, that worked! Thank you! I've been pulling my hair out (figuratively speaking; I'm bald) for a week now! And if I knew how to post my book on here I would for you guys to see. Thank you again!

Ok now I need to throw one more wrench in to the equation. So apparently only auto loans use that formula and cap at 150, mortgages are 500 automatically. How would I go about adding that change? Sorry to seem so helpless here

morning bump

IF(OR(C2={"Checking Account","Credit Card","CD","Savings Account"}),25,(IF(c2={"Mortgage"},500,(C2={"Auto Loan"},IF(D2*0.05>150,150,D2*0.05)))))


This is the formula I'm trying to use to get the "Mortgage" option to show "500" ... I'm stumped again