Using Vlookup for IF statements

[Addy]

Hello there,

I have a rather long IF statement, and as we all know that Excel only
accepts upto 7 IFs statments within the nest.

IF(ISNUMBER(SEARCH("#",D2)),"COUNTRYSTOCK",IF(ISNUMBER(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"))))))))))))))))

Above is my IF statement. Basically it is looking at a part of a
string in column and using a condition.

I am not sure about the Vlookup formula. I have a hunch I could use it
in place of this. Can someone help me get started on it?

 

[PCLIVE]

First you need to create a table. In the example below, I'm using colums A
and B. This table needs to be in ascending order.

# COUNTRYSTOCK
404-BU PACIFIC
404-CA WEST
404-DA ATLANTIC
404-ED WEST
404-LO CENTRAL
404-M1 CENTRAL
404-M2 CENTRAL
404-MJ EAST
404-MO EAST
404-OT EAST
404-QU EAST
404-SA WEST
404-SH EAST
404-VI PACIFIC
404-WI WEST

Then you can use this formula:
=IF(ISERROR(VLOOKUP(D1,A1:B16,2,0)),FALSE,VLOOKUP(D1,A1:B16,2,0))

If the lookup returns an error because it's not found, then the ISERROR will
return False.
HTH,
Paul

 

[Bob Phillips]

=INDEX({"COUNTRYSTOCK","PACIFIC","PACIFIC","WEST","WEST","WEST","WEST","CENTRAL","CENTRAL","CENTRAL","EAST","EAST","EAST","EAST","EAST","ATLANTIC"},
SUMPRODUCT(COUNTIF(D2,{"*#*","*404-BU*","*404-VI*","*404-CA*","*404-ED*","*404-SA*","*404-WI*","*404-M1*","*404-M2*","*404-LO*","*404-OT*","*404-MO*","*404-SH*","*404-QU*","*404-MJ*","*404-DA*"})*{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Addy]

=INDEX({"COUNTRYSTOCK","PACIFIC","PACIFIC","WEST","WEST","WEST","WEST","CEN­TRAL","CENTRAL","CENTRAL","EAST","EAST","EAST","EAST","EAST","ATLANTIC"},
SUMPRODUCT(COUNTIF(D2,{"*#*","*404-BU*","*404-VI*","*404-CA*","*404-ED*","*­404-SA*","*404-WI*","*404-M1*","*404-M2*","*404-LO*","*404-OT*","*404-MO*",­"*404-SH*","*404-QU*","*404-MJ*","*404-DA*"})*{1,2,3,4,5,6,7,8,9,10,11,12,1­3,14,15,16}))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)








- Show quoted text -

Thanks got it to work.