Register
Log In
Unanswered
Home
February 11, 2015
Offline
Hello, I'm learning how to write Excel formulas and wanted advice on this formula. Any better way to write this?
I have Excel 2013, using a PC.
=IF(W6="box",IF(ISNUMBER(SEARCH("RSC",I6)),"half label",IF(W6="box",IF(I6="DIRECT","4X3 label",IF(W6="BOX",IF(I6="DIRECT UPS","UPS LABEL"))),"N/A")),"N/A")
I need to look at column "W" if it states it in a box, then looks at column "I" if shipping "RSC, then I need an RSC label. (I had to search RSC due to both shipping via RSC or RSC Bulk with both use the same label) I tried it with the ISTEXT search, didn't work)
If boxed and direct shipped, then I need a 4x3 label. If boxes and Direct UPS, then I need a UPS label. If none of these are met, I need no label, so N/A.
Any advice would greatly be appreciated.
Thanks so much
Amy
See Answer
July 16, 2010
Offline
Hi Amy,
Please upload a sample file (with some dummy data if you prefer), this will help us to see how your data is structured and give you an alternative, if there is one.
Mynda
July 4, 2016
Offline
Hi Amy.
From your post I gather your data is structured something like this:
Column I contains values such as: "RSC", "RSC BULK", "DIRECT", "DIRECT UPS"
Column W contains values such as "box", "not box"
You want a formula that compares columns I & W to return a value: "half label", "4x3 label", "UPS LABEL", "N/A"
- - - - - - - - - - - - - - -
I'd structure the formula like this:
=IF(AND(W6="box",LEFT(I6,3)="RSC"),"half label",
IF(AND(W6="box",I6="DIRECT"),"4x3 label",
IF(AND(W6="box",I6="DIRECT UPS"),"UPS LABEL","N/A")))
Because IF statements don't like wildcards (to find either "RSC" or "RSC BULK"), I have used the LEFT function.
cheers, Craig
Dashboards
June 25, 2016
Offline
Hi Amy
Based on Craig's formula, you could also simplify to:
=IF(W6="box",IF(LEFT(I6,3)="RSC","half label",IF(I6="DIRECT","4x3 label",IF(I6="DIRECT UPS","UPS LABEL","N/A"))))
SunnyKow
November 8, 2013
Offline
Great solutions already, I'll contribute with another approach, which may be more flexible, in case there are new conditions to be added:
The good thing about this is that the constants arrays {} can be replaced with references to a lookup table with 2 columns: one for INDEX function and the other column for the MATCH function.
Hope it helps.
Answers Post
February 11, 2015
Offline
I just wanted to say "THANK YOU" to everyone. This was so helpful.
Amy
February 11, 2015
Offline
Hello, I have to say this is a wonderful service provide by Mynda and her team. Thanks so much for starting this forum.
I took the formula from Catalin and tried to modify it to work on a different spreadsheet, but can’t get it to work.
I have Exce2013, using a PC.
=IFERROR(INDEX({"4x3 label","UPS","4x3 label"},MATCH(G13&H13,{"LTLBOX","UPSBOX","QUICKBOX"},IF(ISTEXT(SEARCH("Warehouse",O13)),"HALF LABEL",0))),NA())
In column G, is shipping method: LTL, UPS, QUICK.
In column H, is the packaging type: Box, Skid, Carton.
In column O, is the Company name: these are all unique, but if it ships to a Warehouse, I need it to find for a different outcome.
What I need for these to calculate and find….
If G=LTL & H=Box, O=anything other than warehouse = 4x3 label
If G=QUICK & H=Box, O=anything other than warehouse = 4x3 label
If G=LTL & H=Box & O=Warehouse (anywhere in the name) = half sheet label
If G=UPS & H=Box, O =anything other than warehouse = UPS Label
I have added a sample file.
Any advice would great be appreciated.
Thanks so much
Amy
Dashboards
June 25, 2016
Offline
Hi Amy
Give this a try. It is a bit long (no idea how to shorten it
) but it should work.
Modified it based on Catalin's original formula.
=INDEX({"4x3 label","4x3 label","UPS label","half sheet label"},MATCH(H9&G9&IFERROR(SEARCH("warehouse",O9)/SEARCH("warehouse",O9),0),{"BoxLTL0","BoxQUICK0","BoxUPS0","BoxLTL1"},0))
Note that I have removed the IFERROR() as is redundant since it will show the #N/A anyway.
Sunny Kow
Most Users Ever Online: 57
Currently Online: Max Neumann
20 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Ajay Mukhi
Stephen Motte
Rodney Hoyle
michael pitre
Samantha Burleson
Krishnaa Kurugod
David McSwain
Shameem Nassiree
Alanna Henderson
Janel Oz
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
