Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF limit or Open parentheses limit | Excel Discussion (Misc queries) | |||
Problem with data using IF and Nested IF statements possibly??? | Excel Discussion (Misc queries) | |||
nested statements | New Users to Excel | |||
Do I need nested IF statements? | Excel Worksheet Functions |