imbedded if statements
 

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


--
ksusong
------------------------------------------------------------------------
ksusong's Profile: http://www.excelforum.com/member.php...o&userid=25980
View this thread: http://www.excelforum.com/showthread...hreadid=393488

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" wrote in
message ...

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


--
ksusong
------------------------------------------------------------------------
ksusong's Profile:
http://www.excelforum.com/member.php...o&userid=25980
View this thread: http://www.excelforum.com/showthread...hreadid=393488

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


--
ksusong
------------------------------------------------------------------------
ksusong's Profile: http://www.excelforum.com/member.php...o&userid=25980
View this thread: http://www.excelforum.com/showthread...hreadid=393488