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

"James Hamilton" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>