Nested IF statement with VLOOKUP

[James Hamilton]

Hi,

In layman's terms I want a formula that does:

(A) a series of nested IF statements with VLOOKUPS that returns a figure
(B) a series of nested IF statements with VLOOKUPS that returns another figure
(C) adds both (A) and (B) together.

My current formula is:
=IF($O$64=1,VLOOKUP(G33,SL!$G$9:$AH$33,19)*50%+IF($O$71=1,VLOOKUP(G33,SL!$G$9:$AH$33,28,FALSE)))

and it returns a figure, multiplies it by 50%, then adds it to the other
figure from the second part of the formula. The above formula works.

What I want to do is add a series of nested IFs to both parts of the
formula; i.e.

(A)=IF($O$64=1,VLOOKUP(G33,SL!$G$9:$AH$33,19)*50%,=IF($O$64=2,VLOOKUP(G33,SL!$G$9:$AH$33,19)*40%, etc, etc

(B)IF($O$71=1,VLOOKUP(G33,SL!$G$9:$AH$33,28,if(IF($O$71=2,VLOOKUP(G33,SL!$G$9:$AH$33,28, etc, etc.

(C) = (A)+(B)

Please help. I'll be forever in your debt!


James

 

[Biff]

Hi!

How many different variables can $O$64 be?

Use another lookup or maybe choose:

Something like this:

=VLOOKUP(G33,SL!$G$9:$AH$33,19)*VLOOKUP($O$64,Table,2,0)+ the "B" formula

Or:

=VLOOKUP(G33,SL!$G$9:$AH$33,19)*CHOOSE($O$64,50%,40%,etc)+ the "B" formula

Do the same thing with your "B" example.

(B)IF($O$71=1,VLOOKUP(G33,SL!$G$9:$AH$33,28,if(IF($O$71=2,VLOOKUP(G33,SL!$G$9:$AH$33,28,
etc,

There's no difference in the above formula! The lookup is the same no matter
what O71 is! I'm guessing that's just a typo.

Biff