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.

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.

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

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