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

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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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

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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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" <ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com> wrote in
message news:ksusong.1tbfyb_1123286730.8655@excelforum-nospam.com...
>
> 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
>