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
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
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
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
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.
I just wanted to say "THANK YOU" to everyone. This was so helpful.
Amy
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
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
SunnyKow
Thanks! This worked. This will save me so much time. I was doing this manually before, I really appreciate all your help.
Have a wonderful day.
Thanks again
Amy
