Creating a multi-sheet formula to match a contact list that references a drop-down list

Justin Trautmann

Hello to you, Smartsheet community! I am in need of assistance with formulas. I'm so lost in the syntax that I don't know right from wrong, up from down, HAS or COLLECT 😵. I do not identify as a power user with formulas — wizards are my dearest friend — so this push into the advanced features has been a journey.

Is it possible for an INDEX column formula to return a contact list from one sheet to another, by matching two columns; one of which is a dropdown list? If so, how?

Images of relevant columns in the images attached. What we're attempting to accomplish is for the Company Contact Email contact list in the Submitted Food Products sheet to return the CC Email contact list from the Company Contacts sheet by matching the Food Company columns of both sheets. Our difficulty in accomplishing this comes from the Food Company column in Company Contacts being a dropdown list column type.

I attempted HAS and COLLECT in my INDEX formula, but my grasp of syntax and the language of what I actually want has been a total bust. We've been able to match our contact lists from those with single entries in Food Company, but not multiples. At best, we've run into the #NO MATCH or #UNPARSEABLE errors.

It is my hope the above explanation makes sense and a formula solution exists. We're wanting to make the data work for our contacts by making duplicates and other burdensome data entry steps automated.

Assistance, guidance, and solutions are more welcome. 🙇‍♀️🙇‍♂️

Paul Newcome

It is going to be something along the lines of

=INDEX(COLLECT({Email Address Column}, {Food Company Column}, HAS(@cell, [Food Company]@row)), 1)

Paul Newcome

It is going to be something along the lines of

=INDEX(COLLECT({Email Address Column}, {Food Company Column}, HAS(@cell, [Food Company]@row)), 1)

Justin Trautmann

@Paul Newcome , it works! Your mastery of syntax and power over the parenthetical arts has saved us from duplicative data and a cleaner experience on this crazy information super highway. May this Friday bring you as much joy as you've brought to us. Thank you.

Paul Newcome

Happy to help. 👍️