Advanced formula/function question.....

Jeniese Chaffin

I am trying to write a complicated "if" function to search text and provide me with a result if any of the words in quotes are found in the text string.  The formula works in excel but I cannot figure out how to replicate it in Smartsheet. Is there someone that can help? 

This is an example of the formula as it is written in excel (in this example HL$1 and $AP3 are columns in excel): 

=IF(OR(ISNUMBER(SEARCH(HL$1,$AP3)),ISNUMBER(SEARCH("lupine",$AP3)),ISNUMBER(SEARCH("lupini",$AP3)),ISNUMBER(SEARCH("lupin bean",$AP3)),ISNUMBER(SEARCH("lupin seed",$AP3)),ISNUMBER(SEARCH("lupin flour",$AP3)),ISNUMBER(SEARCH("lupin protein",$AP3)),(ISBLANK($AP3))),"HAS","---")

Paul Newcome

Is there the possibility of the text string "lupin" being found that you do NOT want to search for such as "lupina" or "lupin something else"?

If not, then it looks like everything you are searching for begins "lupin" in which case you could use something along the lines of

=IF(CONTAINS("lupin", [Column Name]@row), "HAS", "---")

This will generate an output of "HAS" if it is found regardless of whether it is "lupin", "lupine", "lupine seed", etc. because all of those contain the string of "lupin".

Jeniese Chaffin

thank you, Paul!  that is an excellent idea!  I do have a follow up question if you may be able to help.......  do you have a thought for how to do that formula if all the words in the search string were different?  I am trying to write a formula that codes ingredients in a recipe for possible food allergies.  For example, if I have a list of ingredients, I want to search it for the words "wheat, gluten, barley, modified food starch" - and place "HAS" in the column labeled "Gluten" to indicate a possible gluten allergy.  I am thinking I need to use a "contains or" statement if that's possible?  Or maybe I need to develop a reference sheet?  My confusion lies when I need it to search multiple words to mean the same thing in the same string.........  any thoughts? 

thank you so much for your help!!    

Paul Newcome

There are a number of ways to do it. The most effective route may be to create a listing of everything you want to search for. Then we can use a formula to hit against this list and generate a "HAS". I will throw together a quick sample tomorrow to make sure all of the details are right.

Jeniese Chaffin

thank you, Paul! I am very interested in your recommendations! I have been watching and reading all day and am so close but not there yet!

Paul Newcome

Sorry for the delay. Work got a little crazy. I do have a few questions about your list(s).

You said that you want to put "HAS" in the [Gluten] column if the list contains one or more of "wheat, gluten, barley, modified food starch".

Do you have each of those values in a table somewhere, or can you? I ask because it can be done using an IF/OR and a bunch of CONTAINS functions, but that can get rather long and tedious and hard to manage.

How many different types of allergens are you ultimately searching for? Do you have separate "HAS" type columns for each of those such as Gluten, Nut, Dairy all in their own columns generating a "HAS" result?

If the separate columns scenario is true, how would you feel about a single (2 column) list to manage and a single column that captures each of the allergen types?

GLUTEN..........NUT..........DAIRY

HAS.................................HAS

.......................HAS..........HAS

HAS...............HAS.................

would essentially turn into:

Allergens Present

Gluten, Dairy

Nut, Dairy

Gluten, Nut

based on a single (two column) table that has all allergens listed in one column and the other column shows the allergen type.

All options have a solution. I am just trying to figure out the most efficient way of doing things for you.