How to lookup a company name based on multiple contacts in a cell?

James Vandezande

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.

Leibel S

@Jamesvan5

以下公式应该完成你的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)))," / ")

JeremiahHorstick

@Jamesvan5This 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.

Hi Jackie, <\/p>

Yes, the countif function, which I understand works for single value cases was my first thought. How can we do it formulaically for multiple values in one cell in a multi-select dropdown column?<\/p>

Evaluating each value in the cell rather than all of the contents of the cell as one value. <\/p>

-Jeremiah<\/p>","bodyRaw":"[{\"insert\":\"Hi Jackie, \\nYes, the countif function, which I understand works for single value cases was my first thought. How can we do it formulaically for multiple values in one cell in a multi-select dropdown column?\\nEvaluating each value in the cell rather than all of the contents of the cell as one value. \\n-Jeremiah\\n\"}]","format":"rich","dateInserted":"2020-10-22T18:45:53+00:00","insertUser":{"userID":21126,"name":"JeremiahHorstick","url":"https:\/\/community.smartsheet.com\/profile\/JeremiahHorstick","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-07\/5b\/45\/n5b45eb0c1a48afae956282248c8ad587.jpg","dateLastActive":"2023-08-11T02:52:40+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/266870#Comment_266870","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/266870#Comment_266870

I think at that point the cell would need to be split with

If I have a column with text like this:<\/p>\n

Mac:Air<\/p>\n

Mac:Pro<\/p>\n

Lenovo:Yoga

 <\/p>\n

Using formulas, how can I pull the data that comes before and after the semicolons? I want to end up with 1 column like this:<\/p>\n

Mac<\/p>\n

Mac<\/p>\n

Lenovo

 <\/p>\n

And another column like this:<\/p>\n

Air<\/p>\n

Pro<\/p>\n

Yoga<\/p>\n

 <\/p>\n

Thank you.<\/p>","bodyRaw":"

If I have a column with text like this:<\/p>\n\n

Mac:Air<\/p>\n\n

Mac:Pro<\/p>\n\n

Lenovo:Yoga
\n <\/p>\n\n

Using formulas, how can I pull the data that comes before and after the semicolons? I want to end up with 1 column like this:<\/p>\n\n

Mac<\/p>\n\n

Mac<\/p>\n\n

Lenovo
\n <\/p>\n\n

And another column like this:<\/p>\n\n

Air<\/p>\n\n

Pro<\/p>\n\n

Yoga<\/p>\n\n

 <\/p>\n\n

Thank you.<\/p>","format":"html","dateInserted":"2019-05-28T14:55:47+00:00","insertUser":{"userID":59656,"name":"SYSPK","title":"","url":"https:\/\/community.smartsheet.com\/profile\/SYSPK","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-09\/8a\/a2\/n8aa26d196e6672ed722182cfae07f814.jpg","dateLastActive":"2023-06-14T08:56:39+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/49191\/split-text-to-columns-using-formula","embedType":"quote","name":"Split Text to Columns Using Formula?"}"> https://community.smartsheet.com/discussion/49191/split-text-to-columns-using-formula

I bet@Paul Newcomeknows.

Paul, how do we always end up talking about regular expressions?

Paul Newcome

Where exactly in your screenshot would the formula go?

James Vandezande

@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.

JeremiahHorstick

@sean59916possibly 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 ",".

James Vandezande

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!

Leibel S

@Jamesvan5

以下公式应该完成你的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)))," / ")

James Vandezande

Thanks Leibel S! Both suggested formulas work, but I agree that the Join/Collect method is better.