IF statement to search data in two columns for matching text

anna_ashby
anna_ashby ✭✭
edited 12/09/19 in Formulas and Functions

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!

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!