Employing constant arrays to limit nested IF statements.

[Richard-44]

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

 

[Bernie Deitrick]

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

 

[Aladin Akyurek]

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"}