Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Vlookup with IF statements
Hello there,
I have tried running a nested IF statement with 15 IF clauses. We all know that excel accepts upto a maximum of 7. I need a way to work around this problem. I am sure there is a VLOOKUP solution to this problem. Below is the IF statment I am using. =IF(ISNUMBER(SEARCH("#",D2)),"COUNTRYSTOCK",IF(ISN UMBER(SEARCH("404- BU",D2)),"PACIFIC",IF(ISNUMBER(SEARCH("404- VI",D2)),"PACIFIC",IF(ISNUMBER(SEARCH("404- CA",D2)),"WEST",IF(ISNUMBER(SEARCH("404- ED",D2)),"WEST",IF(ISNUMBER(SEARCH("404- SA",D2)),"WEST",IF(ISNUMBER(SEARCH("404- WI",D2)),"WEST",IF(ISNUMBER(SEARCH("404- M1",D2)),"CENTRAL",IF(ISNUMBER(SEARCH("404- M2",D2)),"CENTRAL",IF(ISNUMBER(SEARCH("404- LO",D2)),"CENTRAL",IF(ISNUMBER(SEARCH("404- OT",D2)),"EAST",IF(ISNUMBER(SEARCH("404- MO",D2)),"EAST",IF(ISNUMBER(SEARCH("404- SH",D2)),"EAST",IF(ISNUMBER(SEARCH("404- QU",D2)),"EAST",IF(ISNUMBER(SEARCH("404- MJ",D2)),"EAST",IF(ISNUMBER(SEARCH("404- DA",D2)),"ATLANTIC","FALSE")))))))))))))))) I would appreciate if someone could help me get stated on this. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Vlookup with IF statements
Here are a couple options....
Option 1) With A1 containing the text to be tested and this list in C1:D16 Code Region BU PACIFIC VI PACIFIC CA WEST ED WEST SA WEST WI WEST M1 CENTRAL M2 CENTRAL LO CENTRAL OT EAST MO EAST SH EAST QU EAST MJ EAST DA ATLANTIC Then put this ARRAY FORMULA (committed with CTRL+****+ENTER, instead of just ENTER) in B1: =IF(SUMPRODUCT(COUNTIF(A1,"*404-"&C2:C16&"*")),INDEX(D2:D16,MATCH(1,COUNTIF(A1,"*4 04-"&C2:C16&"*"),0)),"FALSE") Option 2) Skip the list and just use this regular formula B1: =CHOOSE(SUMPRODUCT(COUNTIF(A1,"*404-"&{"BU","VI","CA","ED","SA","WI","M1","M2","LO","O T","MO","SH","QU","MJ","DA"}&"*"),{1,2,3,4,5,6,7,8 ,9,10,11,12,13,14,15})+1,"FALSE","PACIFIC","PACIFI C","WEST","WEST","WEST","WEST","CENTRAL","CENTRAL" ,"CENTRAL","EAST","EAST","EAST","EAST","EAST","ATL ANTIC") NOTE: Since text wrap will probably impact the display, there are no spaces in either of those formulas. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Addy" wrote: Hello there, I have tried running a nested IF statement with 15 IF clauses. We all know that excel accepts upto a maximum of 7. I need a way to work around this problem. I am sure there is a VLOOKUP solution to this problem. Below is the IF statment I am using. =IF(ISNUMBER(SEARCH("#",D2)),"COUNTRYSTOCK",IF(ISN UMBER(SEARCH("404- BU",D2)),"PACIFIC",IF(ISNUMBER(SEARCH("404- VI",D2)),"PACIFIC",IF(ISNUMBER(SEARCH("404- CA",D2)),"WEST",IF(ISNUMBER(SEARCH("404- ED",D2)),"WEST",IF(ISNUMBER(SEARCH("404- SA",D2)),"WEST",IF(ISNUMBER(SEARCH("404- WI",D2)),"WEST",IF(ISNUMBER(SEARCH("404- M1",D2)),"CENTRAL",IF(ISNUMBER(SEARCH("404- M2",D2)),"CENTRAL",IF(ISNUMBER(SEARCH("404- LO",D2)),"CENTRAL",IF(ISNUMBER(SEARCH("404- OT",D2)),"EAST",IF(ISNUMBER(SEARCH("404- MO",D2)),"EAST",IF(ISNUMBER(SEARCH("404- SH",D2)),"EAST",IF(ISNUMBER(SEARCH("404- QU",D2)),"EAST",IF(ISNUMBER(SEARCH("404- MJ",D2)),"EAST",IF(ISNUMBER(SEARCH("404- DA",D2)),"ATLANTIC","FALSE")))))))))))))))) I would appreciate if someone could help me get stated on this. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Vlookup with IF statements
Check your earlier post.
Addy wrote: Hello there, I have tried running a nested IF statement with 15 IF clauses. We all know that excel accepts upto a maximum of 7. I need a way to work around this problem. I am sure there is a VLOOKUP solution to this problem. Below is the IF statment I am using. =IF(ISNUMBER(SEARCH("#",D2)),"COUNTRYSTOCK",IF(ISN UMBER(SEARCH("404- BU",D2)),"PACIFIC",IF(ISNUMBER(SEARCH("404- VI",D2)),"PACIFIC",IF(ISNUMBER(SEARCH("404- CA",D2)),"WEST",IF(ISNUMBER(SEARCH("404- ED",D2)),"WEST",IF(ISNUMBER(SEARCH("404- SA",D2)),"WEST",IF(ISNUMBER(SEARCH("404- WI",D2)),"WEST",IF(ISNUMBER(SEARCH("404- M1",D2)),"CENTRAL",IF(ISNUMBER(SEARCH("404- M2",D2)),"CENTRAL",IF(ISNUMBER(SEARCH("404- LO",D2)),"CENTRAL",IF(ISNUMBER(SEARCH("404- OT",D2)),"EAST",IF(ISNUMBER(SEARCH("404- MO",D2)),"EAST",IF(ISNUMBER(SEARCH("404- SH",D2)),"EAST",IF(ISNUMBER(SEARCH("404- QU",D2)),"EAST",IF(ISNUMBER(SEARCH("404- MJ",D2)),"EAST",IF(ISNUMBER(SEARCH("404- DA",D2)),"ATLANTIC","FALSE")))))))))))))))) I would appreciate if someone could help me get stated on this. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Vlookup with IF statements
On Apr 12, 4:08 pm, Dave Peterson wrote:
Check your earlier post. Addy wrote: Hello there, I have tried running a nested IF statement with 15 IF clauses. We all know that excel accepts upto a maximum of 7. I need a way to work around this problem. I am sure there is a VLOOKUP solution to this problem. Below is the IF statment I am using. =IF(ISNUMBER(SEARCH("#",D2)),"COUNTRYSTOCK",IF(ISN UMBER(SEARCH("404- BU",D2)),"PACIFIC",IF(ISNUMBER(SEARCH("404- VI",D2)),"PACIFIC",IF(ISNUMBER(SEARCH("404- CA",D2)),"WEST",IF(ISNUMBER(SEARCH("404- ED",D2)),"WEST",IF(ISNUMBER(SEARCH("404- SA",D2)),"WEST",IF(ISNUMBER(SEARCH("404- WI",D2)),"WEST",IF(ISNUMBER(SEARCH("404- M1",D2)),"CENTRAL",IF(ISNUMBER(SEARCH("404- M2",D2)),"CENTRAL",IF(ISNUMBER(SEARCH("404- LO",D2)),"CENTRAL",IF(ISNUMBER(SEARCH("404- OT",D2)),"EAST",IF(ISNUMBER(SEARCH("404- MO",D2)),"EAST",IF(ISNUMBER(SEARCH("404- SH",D2)),"EAST",IF(ISNUMBER(SEARCH("404- QU",D2)),"EAST",IF(ISNUMBER(SEARCH("404- MJ",D2)),"EAST",IF(ISNUMBER(SEARCH("404- DA",D2)),"ATLANTIC","FALSE")))))))))))))))) I would appreciate if someone could help me get stated on this. -- Dave Peterson- Hide quoted text - - Show quoted text - Thanks it works |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup with AND, OR IF statements | Excel Worksheet Functions | |||
Help with Vlookup and If Statements | Excel Discussion (Misc queries) | |||
can I use =if with vlookup statements | Excel Worksheet Functions | |||
VLOOKUP + IF STATEMENTS | Excel Discussion (Misc queries) | |||
VLOOKUP statements | Excel Discussion (Misc queries) |