Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Using Vlookup for IF statements
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Using Vlookup for IF statements
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(ISNU MBER(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?

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,311
Default Using Vlookup for IF statements
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

"Addy" wrote in message
ups.com...
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(ISNU MBER(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?



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Using Vlookup for IF statements
=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" wrote in message
ups.com...
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(ISNU MBER(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?



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Using Vlookup for IF statements
On Apr 12, 2:38 pm, "Bob Phillips" wrote:
=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)

"Addy" wrote in message

ups.com...



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(ISNU MBER(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?- Hide quoted text -

- Show quoted text -

Thanks got it to work.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP and IF Statements cranen Excel Discussion (Misc queries) 2 August 13th 09 10:26 PM
Help with Vlookup and If Statements SunnyM Excel Discussion (Misc queries) 4 February 9th 06 12:45 AM
can I use =if with vlookup statements suzyque Excel Worksheet Functions 12 January 31st 06 04:48 PM
VLOOKUP + IF STATEMENTS Excel Discussion (Misc queries) 6 April 1st 05 08:43 AM
VLOOKUP statements Jennifer Kramer Excel Discussion (Misc queries) 1 January 20th 05 05:51 AM


All times are GMT +1. The time now is 11:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement