Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

nested IF( ) limits exceeded

0 views
Skip to first unread message

Paul Reeves

unread,
Aug 6, 2003, 12:15:46 AM8/6/03
to
I have situation where I am exceeding the nested IF()
limits.

example of IF() gone wild
=IF(AND(P2029="MC",L2029="X"),Collection&" "&Reader,
IF(AND(P2029="MC",L2029="N"),Collection&" "&Nonfiction,
IF(AND(P2029="MC",L2029="T"),Collection&" "&Tales,
IF(AND(P2029="MP",L2029="X"),Plus&" "&Reader,
IF(AND(P2029="MP",L2029="N"),Plus&" "&Nonfiction,
IF(AND(P2029="PM",L2029="X"),Spanish&" "&Reader,
IF(AND(P2029="PM",L2029="N"),Spanish&" "&Nonfiction,
" ")))))))

I can see myself adding at least 3 more If() statements
MC, S
MP, S
PM, S

Any help is appreciated.

Thanks,

Orlando Magalhães Filho

unread,
Aug 6, 2003, 12:44:34 AM8/6/03
to
Hi Paul Reeves,

Try to use CHOOSE() function something like this:

=CHOOSE(1*(P2029="MC")*(L2029="X")+2*(P2029="MC")*(L2029="N")+3*(P2029="MC")
*(L2029="T")+4*(P2029="MP")*(L2029="X")+5*(P2029="MP")*(L2029="N")+6*(P2029=
"PM")*(L2029="X")+7*(P2029="PM")*(L2029="N"),Collection&"
"&Reader,Collection&" "&Nonfiction,Collection&" "&Tales,Plus&"
"&Reader,Plus&" "&Nonfiction,Spanish&" "&Reader,Spanish&" "&Nonfiction) and
so on until 30 arguments


HTH

---
Orlando Magalhães Filho

(So that you get best and rapid solution and all may benefit from the
discussion, please reply within the newsgroup, not in email)

"Paul Reeves" <preev...@earthlink.net> escreveu na mensagem
news:05f501c35bd1$68f672a0$a501...@phx.gbl...

Arvi Laanemets

unread,
Aug 6, 2003, 12:59:16 AM8/6/03
to
Hi


=IF(P2029="MC","Collection",IF(P2029="MP","Plus",IF(P2029="PM","Spanish")))&
" "&IF(L2029="X","Reader",IF(L2029="N","Nonfiction";IF(L2029="T","Tales")))

You are free up to 7*7 combinations


Arvi Laanemets


"Paul Reeves" <preev...@earthlink.net> wrote in message
news:05f501c35bd1$68f672a0$a501...@phx.gbl...

Mike

unread,
Aug 6, 2003, 12:20:37 AM8/6/03
to
=VLOOKUP(P2029,SheetX!A1:B3,2,FALSE)&" "&VLOOKUP(L2029,SheetX!D1:E4,2,FALSE)

SheetX
A B C D E
1 MC Collection X Reader
2 MP Plus N Nonfiction
3 PM Spanish T Tales
4 S Sience

--
Mike

Ref to "Paul Reeves" <preev...@earthlink.net> wrote in message news:05f501c35bd1$68f672a0$a501...@phx.gbl...

Paul

unread,
Aug 7, 2003, 4:05:06 PM8/7/03
to
Mike,

This is great.

Thanks,
>-----Original Message-----
>=VLOOKUP(P2029,SheetX!A1:B3,2,FALSE)&" "&VLOOKUP


(L2029,SheetX!D1:E4,2,FALSE)
>
>SheetX
> A B C D E
>1 MC Collection X Reader
>2 MP Plus N Nonfiction
>3 PM Spanish T Tales
>4 S Sience
>
>--
>Mike
>
>Ref to "Paul Reeves" <preev...@earthlink.net> wrote in
message news:05f501c35bd1$68f672a0$a501...@phx.gbl...

>.
>

0 new messages