IF statement to search data in two columns for matching text
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!
Comments
-
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"))
-
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.
-
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.
-
That worked perfectly! Thank you so much for your help!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!