Can I use Join(Collect with multiple entry drop down?

Brandon Morales

Hi, I have a formula below that looks at 2 sheets and when the "PLY-Code Needed" from the 1st sheet finds a match on sheet 2, it gives me the "Description" from sheet 2. It works fine but as shown in row 131, there are 2 "PLY-Codes" and this makes this unable to pul the info. Any advice?

Here is my Current formula:

=JOIN(COLLECT({Ply List With Codes Range 1}, {Ply List With Codes Range 2}, =[PLY-Code Needed]@row ), " / ")

Paul Newcome

You need a HAS function like so:

=JOIN(COLLECT({Ply List With Codes Range 1}, {Ply List With Codes Range 2}, HAS(@cell, [PLY-Code Needed]@row)), " / ")

Brandon Morales

It comes up as unparseable when i use the formula as you have it written. Im sure im missing something…

Paul Newcome

You misplaced a closing parenthesis and added an extra one at the end.

You have

@row), " / ")))

I have

@row)), " / ")

Give it a shot copy/pasting it exactly as I have it in my last comment directly from here into your sheet and see if that works.

Brandon Morales

ok, yea, i knew i had something off…

using the has function actually didnt work- but using contains instead did the trick- thank you!

Paul Newcome

That's interesting. The HAS function was created specifically for multi-select dropdown type columns. It may be that the syntax needed flipped around.

HAS([PLY-Code Needed]@row, @cell)

This is also the standard syntax of CONTAINS (no idea why they flipped it for the two functions), so that may be why that one worked.