Is it possible to do a lookup in a field that has multiple data in a cell.
I want to check if the emails under contact list are present in the table below.
Contact list
john.doe@abc.com,sally@abc.com,rall@abc.com
I want to check if any of those email addresses are present in another sheet the looks like this:
Email Status
john.doe@abc.com Yes
frank@abc.com Yes
sally@abc.com Yes
brand@abc.com Yes
rall@abc.com Yes
Answers
-
Is the contact list set up to house multiple contacts in a single cell, or are they comma separated values as in your example above?
-
The contact list is setup to separate each value by a comma
-
You would need to use a Multi-select Dropdown Type helper column on the Contact List sheet. Then you would put this in the helper column:
=SUBSTITUTE([Contact List]@row, ",", CHAR(10))
Then to check the box you would use:
=IF(HAS([Helper Column]@row, Email:Email), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!