Formula for if cell contains one of many things, return that thing

jdschiff

I'm looking to create a formula for if a cell contains one of a list of things, it will return that thing. In excel, I would use the method found in this article (https://exceljet.net/formula/if-cell-contains-one-of-many-things) but have not been able to recreate in Smartsheet.

Mike TV

If you have a column named Package and it were looking for red, green, or yellow, this would be your formula.

=IF(Package@row = "green", Package@row, IF(Package@row = "red", Package@row, IF(Package@row = "yellow", Package@row, "")))

If you needed to add on more to the formula above, you'd erase the ""))) at the end and after the comma just keep adding more IF statements. When you're done with all of them you need to look for a return the true value for, just end it with the false "" after the last comma and it'll add all the ) that it needs to.

jdschiff

Thank you, @Matt Cohoon! This is definitely an option.

Ideally we would like to check against a list of items since we have dozens of items that will change over time. Would that be possible?

Mike TV

Yes it's possible. You'll want to set up a database sheet such as this:

You'll want to create more rows than you currently have the need for so you can grow over time. Make sure to add as many to the maximum you think you'll need to search for at any time in the future.

Then on your working sheet you'll have the column you're searching for results in and the one you write your formula in where you want the values returned to. Such as this:

Here's your formula:

=IF([Search In]@row = {Search For 1}, [Search In]@row, IF([Search In]@row = {Search For 2}, [Search In]@row, IF([Search In]@row = {Search For 3}, [Search In]@row, IF([Search In]@row = {Search For 4}, [Search In]@row, ""))))

The {Search For 1} is a reference to a single cell in another sheet which is your database sheet. As you can see, we searched for tree, bird, house, and fish within the Search In column and found everything except for candy since candy isn't in the database sheet. It will take some time to set it all up depending on how many things you need to search for initially and how big you want to build it for scaling.

The reason for the placeholder checkbox column on the database sheet is because you cannot create a reference to an empty cell.

jdschiff

@Matt Cohoon Sorry, I see I didn't mention that the item will be within a string of text, such that if the cell contains "I'd like to purchase a fish", we would want it to return "fish", for a list of different items that may grow over time.

Mike TV

=IF(CONTAINS({Search For 1}, [Search In]@row), {Search For 1}, IF(CONTAINS({Search For 2}, [Search In]@row), {Search For 2}, IF(CONTAINS({Search For 3}, [Search In]@row), {Search For 3}, IF(CONTAINS({Search For 4}, [Search In]@row), {Search For 4}, ""))))

Mike TV

@jdschiff Did you have a chance to try out the new formula I posted? It should do what you needed and once set up should be quick to add/remove search items.