Employing constant arrays to limit nested IF statements.
 

I'm using Excel 2002, with SP-3 installed.

I'm trying to incorporate a constant array into an IF formula. If cell E3
contains one of 3 text entries (let's say A, B or C), I want another cell
(E4) to respond with a given text entry (let's say Z); if E3 contains one of
4 other text entries (let's say D, E, F or G) I want E4 to respond with a
different text entry (let's say Y); or if E3 contains one of 5 other text
entries (let's say H, I , J, K, or L) I want E4 to respond with a
third-option text entry (let's say X).

Because simply nesting these 11 options overloads the 7-nest limit in IF; I
want to use constant arrays for each of the three groups of possibilities,
and thereby set up only 3 nested IF/then formulae within a single formula.

But every time I use =IF(E3={"A","B","C"},"Z",8) only "A" creates "Z". Any
other data (including "B" & "C") produce the negative (the 8). I've tried
Naming the array but that doesn't work either.

Any ideas?

Thanks

Richard,

An easy workaround is to run all the possible values together, as long as
they are sufficiently unique:

=IF(NOT(ISERROR(FIND(A1,"Word1 Word2 Word3
Word4"))),"Value1",IF(NOT(ISERROR(FIND(A1,"Word5 Word6 Word7
Word8"))),"Value2",IF(NOT(ISERROR(FIND(A1,"Word9 Word10 Word11
Word12"))),"Value3","Not Found")))

(All on one line) - make it as long as you want.

HTH,
Bernie
MS Excel MVP

"Richard-44" wrote in message
...
I'm using Excel 2002, with SP-3 installed.

I'm trying to incorporate a constant array into an IF formula. If cell E3
contains one of 3 text entries (let's say A, B or C), I want another cell
(E4) to respond with a given text entry (let's say Z); if E3 contains one
of
4 other text entries (let's say D, E, F or G) I want E4 to respond with a
different text entry (let's say Y); or if E3 contains one of 5 other text
entries (let's say H, I , J, K, or L) I want E4 to respond with a
third-option text entry (let's say X).

Because simply nesting these 11 options overloads the 7-nest limit in IF;
I
want to use constant arrays for each of the three groups of possibilities,
and thereby set up only 3 nested IF/then formulae within a single formula.

But every time I use =IF(E3={"A","B","C"},"Z",8) only "A" creates "Z".
Any
other data (including "B" & "C") produce the negative (the 8). I've tried
Naming the array but that doesn't work either.

Any ideas?

Thanks

Also...

=IF(LOOKUP(E3,{"A";"B";"C";"D";"E";"F";"G";"H";"I" ;"J";"K";"L"})=E3,
LOOKUP(E3,{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J" ;"K";"L"},
{"Z";"Z";"Z";"Y";"Y";"Y";"Y";"X";"X";"X";"X";"X"}) ,"")

Or:

=IF(LOOKUP(E3,INDEX(Table,0,1))=E3,LOOKUP(E3,Table ),"")

where Table refers to a 2-column range sorted on its first column which
associates:

{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L "}

with

{"Z";"Z";"Z";"Y";"Y";"Y";"Y";"X";"X";"X";"X";"X "}

Richard-44 wrote:
I'm using Excel 2002, with SP-3 installed.

I'm trying to incorporate a constant array into an IF formula. If cell E3
contains one of 3 text entries (let's say A, B or C), I want another cell
(E4) to respond with a given text entry (let's say Z); if E3 contains one of
4 other text entries (let's say D, E, F or G) I want E4 to respond with a
different text entry (let's say Y); or if E3 contains one of 5 other text
entries (let's say H, I , J, K, or L) I want E4 to respond with a
third-option text entry (let's say X).

Because simply nesting these 11 options overloads the 7-nest limit in IF; I
want to use constant arrays for each of the three groups of possibilities,
and thereby set up only 3 nested IF/then formulae within a single formula.

But every time I use =IF(E3={"A","B","C"},"Z",8) only "A" creates "Z". Any
other data (including "B" & "C") produce the negative (the 8). I've tried
Naming the array but that doesn't work either.

Any ideas?

Thanks