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

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    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)))," / ")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!