Finding package details.

**rmhoutz** · 05-19-2012, 09:40 AM

I’m working on an excel (2010) sheet that I want excel to look for the letter following the item# and if it ends in B then return one value if it ends is C return another value.

Example
A1 contains item 0123456789C and the quantity ordered in cell E8/D8= less than or equal to 20 (cartons that’s what the C stands for) then return item should be B pack or Bulk pack. I’m not sure I’m going about this the correct way so any alternative suggestions would be great. This is the formula I’m currently using. =IF(ISNUMBER(SEARCH("C",B8)),E8/D8,0)=AND(IF(E8/D8>20,E8/D8,"B Pack Needed")). But it’s returning a false statement for 21/1.
Attached is a copy of the excell form.

**JieJenn** · 05-19-2012, 10:00 AM

Try =IF(AND(RIGHT(B8,1)="C",D8<=20,E8<=20),"Bulk Pack","")

If you didn't mention what you want to do with other scenarios, so I stop right there.

**rmhoutz** · 05-19-2012, 10:30 AM

If I understand correctly by other scenarios you’re referring to what if cell B8 ends in B then I want the value of E8/D8 to be the Bulk quantity in cell HI. From there I have a vlookup that will give me the Box size and quantity of boxes on sheet 2 Vlookup tables. See attachment for examples.

**JieJenn** · 05-19-2012, 10:46 AM

My bad. I interpret E8/D8 as E8 or D8 haha.. Let's try again

=IFERROR(IF(AND(E8/D8<=20,RIGHT(B8,1)="C"),"B Pack Needed","B Pack Not Needed"),"")

**rmhoutz** · 05-19-2012, 01:10 PM

Awesome thank you very much with just a little tweaking as I’m sure you intended It works great.
Thank you for taking the time to help me with this you have been a great help.
My end formula incase you want to see is =IFERROR(IF(AND(E8/D8<=20,RIGHT(B8,1)="C"),E8/D8,0),"") for carton pack and =IFERROR(IF(AND(E8/D8>=21,RIGHT(B8,1)="B"),E8/D8,0),"") for bulk pack. Thank you again and you have a great weekend.