Have a cell's content be based from a multi-select dropdown

scott.95

Hi, I might be overthinking this, but after reading several posts, I can't find what I'm looking for. I have a multi-select column that denotes buildings where I have projects. I want another column next to that that populates the building's abbreviation(s) from the multi-select dropdown. For a single building, using...

=INDEX({Sht_BUILDING_DATA-Bldg_Abbrv}, MATCH([PROJ BUILDING NAME]@row, {Sht_BUILDING_DATA-Bldg_Name}, 0))

works just fine. When I have 2 or more buildings selected in the dropdown, I get #NO MATCH. The Index/Match formula pulls from another sheet where each building has a row with a name column and abbreviation column.

My attempts at join/collect and HAS haven't proven successful. Any help is appreciated. The building abbreviation is to help with a report to keep the columns narrow.

Paul Newcome

Give this a try...

=JOIN(COLLECT({Abbreviation Column}, {Building Name Column}, HAS([Proj Building Name]@row, @cell)), CHAR(10))

Paul Newcome

Give this a try...

=JOIN(COLLECT({Abbreviation Column}, {Building Name Column}, HAS([Proj Building Name]@row, @cell)), CHAR(10))

scott.95

Thank you, thank you. That worked.

Paul Newcome

Happy to help. 👍️