imbedded if statements

[ksusong]

i have a imbedded if statement of 8, that returns a cell reference from
various ranges. what is another option to get 9 or 10 statements in one
formula. the formula is:



=IF(P7="M",(VLOOKUP(O7,$X$19:$Z$27,3)),IF(P7="G",(VLOOKUP(O7,$X$30:$Z$41,3)),IF(P7="BA",(VLOOKUP(O7,$X$45:$Z$57,3)),IF(P7="F",7%,IF(P7="UTC",9%,IF(P7="UTM",7%,IF(P7="CF",2%,1)))))))

i need to add one more
if(p7="c",(vlookup(o7,$x$60:$z$68,3))

ANY help would be appreciated
kat

 

[Bob Umlas]

You can use the technique of ANDing the conditions instead of nesting them:

=IF(P7="M",VLOOKUP(O7,$X$19:$Z$27,3),"")&IF(P7="G",VLOOKUP(O7,$X$30:$Z$41,3),"")&IF(P7="BA",VLOOKUP(O7,$X$45:$Z$57,3),"")&IF(P7="F",7%,"")&IF(P7="UTC",9%,"")&IF(P7="UTM",7%,"")&IF(P7="CF",2%,1)&IF(P7="c",VLOOKUP(O7,$X$60:$Z$68,3),"")

Note each is a series of complete if-statements connected with &. For better
readability:
=IF(P7="M",VLOOKUP(O7,$X$19:$Z$27,3),"") &
IF(P7="G",VLOOKUP(O7,$X$30:$Z$41,3),"") &
IF(P7="BA",VLOOKUP(O7,$X$45:$Z$57,3),"") &
IF(P7="F",7%,"") &
IF(P7="UTC",9%,"") &
IF(P7="UTM",7%,"") &
IF(P7="CF",2%,1) &
IF(P7="c",VLOOKUP(O7,$X$60:$Z$68,3),"")

Bob Umlas
Excel MVP

 

[ksusong]

Thanks Bob! your formula worked with one correction:

=IF(P7="M",VLOOKUP(O7,$X$19:$Z$27,3)),"") &
IF(P7="G",VLOOKUP(O7,$X$30:$Z$41,3)),"") &
IF(P7="BA",VLOOKUP(O7,$X$45:$Z$57,3)),"") &
IF(P7="F",7%,"") &
IF(P7="UTC",9%,"") &
IF(P7="UTM",7%,"") &
IF(P7="CF",2%,"") &
IF(P7="c",VLOOKUP(O7,$X$60:$Z$68,3)),"")

I needed to add another parenthesis to close each vlookup statement.
Thank you for this. The answer was so simple yet it eluded me.
kat