How to lookup a company name based on multiple contacts in a cell?
I'm trying to figure out if there's a way to create a formula that can look at MULTIPLE contacts in a cell and determine if ONE company matches from a project directory.
In our Action Item log for the "Assigned To" field, this works fine if there is ONE contact:
=IFERROR(INDEX({Dir-Company}, MATCH([Assigned To]@row, {Dir-Name}, 0)), "-")
Dir-Company is the reference to the Company column in my project directory; Dir-Name is the Name column from the directory.
So, I'm wondering if there's a way to allow the formula to still pull the company name if all the contacts are from the same company. If multiple matches, report "Varies" - or something like that.
Best Answer
-
The below formula should accomplish what you are looking for:
=if(count(distinct(collect({Dir-Company},{Dir-Name},has([Assigned To]@row,@cell))))>1,"Varies",index(collect({Dir-Company},{Dir-Name},has([Assigned To]@row,@cell)),1))
I would recommend to possibly just do a join and collect and have all the companies associated with any of the contacts shown. For that the formula below should work:
=join(distinct(collect({Dir-Company},{Dir-Name},has([Assigned To]@row,@cell)))," / ")
Answers
-
@Jamesvan5 This stumps me too.
How do we evaluate each value in a multi-select column with a formula?
Once you reference the the multi-select cell the cell's contents seem to be a string type.
I think at that point the cell would need to be split with
I bet @Paul Newcome knows. 😀
Paul, how do we always end up talking about regular expressions?
-
Where exactly in your screenshot would the formula go?
-
@Paul Newcome - the formula is in our Action Items Log, used to lookup the company of the person assigned to an item so the log can be sorted or filtered by "responsible company." We used to have the Assigned To column as a single-select contact, but our users wanted to assign multiple people to an item if necessary.
-
@sean59916 possibly has part of the solution with the example above regarding how to split a cell among other cells using the "find" formula along with string (text) manipulation formulas.
In this case I belive the delimiter is CHAR10 rather than ",".
-
If I understand that correctly, I would have to make some additional columns somewhere in the Action Items Log - maybe even hidden - and then use the FIND formula to evaluate those columns to eventually lookup the company?
Sounds possible, but there's no control over how many contacts someone adds to that field. I still believe that only ONE person should have the LEAD on an action item - then all others can be in the Courtesy Copy column. That would solve my formula problem!
-
The below formula should accomplish what you are looking for:
=if(count(distinct(collect({Dir-Company},{Dir-Name},has([Assigned To]@row,@cell))))>1,"Varies",index(collect({Dir-Company},{Dir-Name},has([Assigned To]@row,@cell)),1))
I would recommend to possibly just do a join and collect and have all the companies associated with any of the contacts shown. For that the formula below should work:
=join(distinct(collect({Dir-Company},{Dir-Name},has([Assigned To]@row,@cell)))," / ")
-
Thanks Leibel S! Both suggested formulas work, but I agree that the Join/Collect method is better.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!