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? |
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? |
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? |
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. |
All times are GMT +1. The time now is 09:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com