Nested IF Using SEARCH functions

**SoleAris** · 01-24-2012, 01:44 PM

Hey all,

So I thought this one was an easy formula, however I can't seem to get the formula right.

I have a PivotTable that displays the shipping information for each "carton" that we ship for a particular order. In that PivotTable, information such as the dimensions, the contents, etc. are displayed. However, I'd like to get the cartons to display in the PivotTable in a logical order (our big products cartons first, followed by smaller product cartons and then finally our miscellaneous parts cartons for the order). My solution to this was to create a field that assigns a value to each item depending on the description of that item. A "big product" would be assigned a value of "A", small products a value of "B", and parts "C".

I figured I could just create multiple nested IF statements utilizing the SEARCH function (to look for a particular word in the item description field) to give me the values of {A, B, C} if it matched one of the words. However, I can only get the first IF statement to give me the results I want, and the rest of the values in my formula field return blanks. Here is an example of my code to get my "A" value:

Table Range is A9:I58, with column E being my Item Description field

=IFERROR(IF(SEARCH("*BIG*", 'Order Details'!$E9)=1, "A"), "")

This returns me the value "A" for any description containing the word "BIG" in it. Now when I try to add onto this IF statement by nesting another IF in it, I simply get a blank. I am placing it in the if [false] part of the first IF statement, is that my error? Example:

=IFERROR(IF(SEARCH("*BIG*", 'Order Details'!$E9)=1, "A", IF(SEARCH("*SMALL*", 'Order Details'!$E9)=1, "B")), "")

Any help would be greatly appreciated..I feel like this is an easy fix and I'm just overlooking something here. Also, is this the easiest way to go about ordering my set of data displayed in my PivotTable? I plan to take this formula field and insert it (but hide it) into the PivotTable to use as the sort for my list of cartons.

-------------------------------------------------------------------------

Also, for bonus reps, any solutions to this?

So my ODBC source imports the contents of each order based upon an entered order number. Let's say, however, that one "big product" imports with a quantity of 2, rather than 1. Since it's a big product, it will need to ship in 2 separate cartons, however it imports as 1 row of data. In the table, I've just been assigning the value of "1, 2" for the Carton# field to signify that this item will require cartons # 1 and 2. This works, however it's not as pretty or uniform with the rest of the cartons being individually listed. Is there a way to separate these two carton values into 2 rows of data eventually getting put into my PivotTable? If you need a visual example of this, please let me know.