IF statement to search data in two columns for matching text

anna_ashby

I am somewhat new to SmartSheets, and would like to know if there is a more concise way of doing what I'm trying to do.

I am using sample names for the purposes of my question. I have one column of data [Data Column] that contains different city names, some blanks, and some text that is not city names. What I need to do for each row is check against the text in that column, and if it is any one of a set of 45 city names then the cell will return the text "Set 1" and if it is any one of a set of 60 other city names then the cell will return the text "Set 2". If it is blank or contains text that doesn't match any of the cities, it will return "Error".

Example of truncated formula:

=IF(OR(FIND("City A", [Data Column]@row) > 0, FIND("City B", [Data Column]@row) > 0, ...Here is where this repeats 45 times for each of the 45 cities...), "Set 1", IF(OR(FIND("City Q", [Data Column]@row) > 0, FIND("City R", [Data Column]@row) > 0 ...Here is where this repeats 60 times for each of the 60 other cities...), "Set 2", "Error"))

 

When I tried it with just the 45 cities it worked great, but when I added the rest, I think I came up to a limit for how long a formula could be because it cut it off towards the end when I pasted it in the cell.

It seems like there's a way I can have a column for each list of cities [Set 1] and [Set 2], then my formula would look at the contents of the columns and determine if the text in my cell is in Set 1 or Set 2, or neither, but I cannot figure out yet how to make it work. Any assistance would be much appreciated!

Paul Newcome

There is a limit of 4,000 characters per cell that you probably hit.

 

Try something like this instead...

 

Put the list for Set 1 in one column and the list for Set 2 in another. From there you have a couple of different options on how to search the city name. I'll try to stick with the most simple here.

 

=IF(FIND([Data Column]@row, JOIN(COLLECT([Set 1]:[Set 1], [Set 1]:[Set 1], ISTEXT(@cell)), ", ")) > 0, "Set 1", IF(FIND([Data Column]@row, JOIN(COLLECT([Set 2]:[Set 2], [Set 2]:[Set 2], ISTEXT(@cell)), ", ")) > 0, "Set 2"))

anna_ashby

Thank so much Paul!

How could I use this formula with a cross-sheet reference for the data in the two columns? I am coming up to my column limit in my sheet so I created a new sheet just for these two columns to save space.  

 

 

Paul Newcome

Put the two columns on a separate sheet. Start typing the formula. when it gets to where you need to specify a range, click on the link in the text box below the formula to "Reference another sheet". Select the sheet with your table on it, and then select the appropriate column by clicking on the column header. Once you click on "Insert Reference" in the bottom right corner, it will take you back to the sheet that you are entering your formula into and have the reference there already.

anna_ashby

That worked perfectly! Thank you so much for your help! 

Paul Newcome

Happy to help!