# Multi Select Look up

edited 12/09/19

I'm trying to see if anyone has any ideas of a formula that can been written for the following example.

I'm trying to get the contact(mail) in sheet 2 to populate sheet 1(Approval Seq 1 Contact) column.

There is 2 criteria (Division and Seq.1) on sheet 1 need to match (Division, Sequence #) on sheet 2 to return the contact (Contact (Email) to Sheet 1 (Approval Seq 1 Contact).

Sequence # is a Multi Select (dropdown list)

Thanks for the help

Tags:

• Employee

Hello,

Happy to help create a formula that looks up a contact value from a source when the Division Column and Sequence Column match on both sheets. To achieve this you can utilize a cross-sheet JOIN(COLLECT()) formula similar to the below.

=JOIN(COLLECT({Source sheet Contact Column}, {Source sheet Division Column}, Division@row, {Source sheet Sequence Column}, Sequence@row), " , ")

The above formula would be placed in the contact column you which the contact to populate in. {Source sheet Contact Column}, {Source sheet Division Column}, and {Source sheet Sequence Column} are cross-sheet references that you will want to link to their respective columns on the source sheet, to achieve this highlight them once in the contact cell and select 'Reference Another Sheet'. Division@row and Sequence@row, will ensure that the criteria the formula is looking for on the source sheet are the value that appears in the columns on your recipient sheet. The topics we discussed are further outlined by the Help Center articles below.

JOIN(COLLECT()): https://help.smartsheet.com/function/collect

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

• Eric,

Thanks for the helpful tips but for some reason it returns the contact as a text string and not the actual contact in the column.  Do you have a trick or work around to return the actual contact?

Thanks

Scott

• Overachievers Alumni

I'm having this exact problem!

Join/Collect works to bring the data but its one solid text string instead of the separate options that are part of my list of options.

Kelly Drake (she/her/hers)

STARBUCKS COFFEE COMPANY| business optimization product manager

• ✭✭✭✭✭✭

Hi Kelly,

Try something like this.

In this example, the values are stored in row 1, and when the statement is true, we collect the values together, and then we use CHAR10 to break them up, so they become their options in a multi-select list.

=JOIN(COLLECT(ColumnOne\$1:ColumnThree\$1; ColumnOne@row:ColumnThree@row; @cell <> ""); CHAR(10))

The same version but with the below changes for your and others convenience.

=JOIN(COLLECT(ColumnOne\$1:ColumnThree\$1, ColumnOne@row:ColumnThree@row, @cell <> ""), CHAR(10))

Depending on your country, you'll need to exchange the comma to a period and the semi-colon to a comma."

Would that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Hi!

I know this is an older forum but I have been trying to find a solution for pushing many single check columns to the selections in a multi-select dropdown and have been having issues. When using the join(collect...) formula from above (and other similar forum discussions) I only get a true/false return...for each column so it looks like "false true false false false true true false false...etc" but I need the column names to populate as values instead of the true/false. I'm unsure how to include this formula and any assistance would be greatly appreciated!

• Employee

Is this in the same sheet? I would personally write a long nested IF statement to check if the box is checked or not and then if it is, return the Column Name:

=IF([Column 1]@row = 1, "Column 1" + CHAR(10)) + IF([Column 2]@row = 1, "Column 2" + CHAR(10)) + IF([Column 3]@row = 1, "Column 3" + CHAR(10))

And so on.

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Genevieve

It was close... I believe I got it to work with using the join and collect function.

Thanks for the help.

• Yes I was able to get it to work with a JOIN(COLLECT) after setting an absolute row reference that was a copy of the col headers (there were 37 col so a nested statement was an unwieldy option). Thank you for your input!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!