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

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)))," / ")

JeremiahHorstick

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

https://community.smartsheet.com/discussion/comment/266870#Comment_266870

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

https://community.smartsheet.com/discussion/49191/split-text-to-columns-using-formula

I bet @Paul Newcome knows. 😀

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

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

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

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)))," / ")

James Vandezande

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