February 11, 2015
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
July 4, 2016
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
VIP
Trusted Members
June 25, 2016
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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
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
VIP
Trusted Members
June 25, 2016
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
1 Guest(s)