How to Create a Sort-Key In Excel Based On Values in Text Fields

While working on a client's inventory spreadsheet, I wanted a way to group and sort the rows based on the presence of various values in text columns. For example, if an inventory row had the word "Chocolate" or "Cocoa" anywhere in the B cell, I wanted to include it in the chocolate group, and I wanted to be able to sort the chocolate group after condiments. But I wanted chocolate sauce to go into condiments, not chocolate, based on the fact that it had the word "Condiments" in the I column.

I created a "Group" column, I entered the following formula into the first cell in the column (in row 2, since row 1 was all headings), and I copied the formula down into every cell below the first one.

=
IF(ISNUMBER(SEARCH("Condiments", I2)), "03 - Condiments",
IF(ISNUMBER(SEARCH("Chocolate", B2)), "04 - Chocolate",
IF(ISNUMBER(SEARCH("Cocoa", B2)), "04 - Chocolate",
IF(ISNUMBER(SEARCH("Dairy", I2)), "01 - Dairy and Eggs",
IF(ISNUMBER(SEARCH("Cheese", I2)), "02 - Butter and Cheese",
IF(ISNUMBER(SEARCH("Dry Goods", I2)), "05 - Dry Goods",
IF(ISNUMBER(SEARCH("Meat", I2)), "06 - Meat/Fish",
IF(ISNUMBER(SEARCH("Fruit for Cups", I2)), "07 - Prepared/Frozen Produce",
IF(ISNUMBER(SEARCH("zShelf WI4", I2)), "07 - Prepared/Frozen Produce",
IF(ISNUMBER(SEARCH("Jay FreeZe", I2)), "07 - Prepared/Frozen Produce",
IF(ISNUMBER(SEARCH("Produce", I2)), "08 - Produce",
IF(ISNUMBER(SEARCH("zShelf WI2", I2)), "09 - Herbs",
IF(ISNUMBER(SEARCH("Spices", I2)), "10 - Spices",
IF(ISNUMBER(SEARCH("Packaging", I2)), "11 - Paper/Packaging Etc",
"99 - Unknown"))))))))))))))

The formula uses nested IF checks, mimicking the else-if logic found in other languages. By putting each check on its own line, I made it easy to reorder the rules in a text editor (and then update the formula). And then you just have to be careful to make sure there's one closing-parenthesis for each of the IF calls.

Regarding the ISNUMBER() function calls, that's just one way to determine whether SEARCH() found a match anywhere in the text cell. If you want to match only in case your search term occurs at the start of a cell, you can test whether the return value of SEARCH() equals 1. Note that you can also use wildcards in your search term, and that SEARCH() is case-insensitive.