JOIN(COLLECT) for multiple values formula is pulling all values in reference sheet

Allison Mandelkow1

Hello,

I have an LMS Subjects (some have multiple Subjects listed) column and an additional Subject IDs Combined column. I also have a separate sheet with all of the Subjects and ID's that go with each subject that I am referring the function to. I have tried several different options.

=INDEX(COLLECT({Subjects - VLookup}, {Subjects - VLookup Range 2}, [LMS Subjects]@row <> ""), 1) + "; " + JOIN(COLLECT({Subjects - VLookup Range 3}, {Subjects - VLookup Range 2}, [LMS Subjects]@row <> ""), "; ")
**This formula shows all of the ID's in my list

=JOIN(COLLECT({Subjects - VLookup}, {Subjects - VLookup Range 2}, [LMS Subjects]@row), 1 + "; ")
**This formula shows only one ID (if there are multiple Subjects it shows nothing)

=JOIN(COLLECT({Subjects - VLookup}, {Subjects - VLookup Range 2}, [LMS Subjects]@row)) + "; " + JOIN(COLLECT({Subjects - VLookup}, {Subjects - VLookup Range 2}, [LMS Subjects]@row))
**This formula only shows ;

Does anyone know what I am doing incorrectly? I want the formula to show multiple Subject ID's in the combined column separated by a "; ".

Columns.png

Reference Sheet.png

MichaelTCA

Hello @Allison Mandelkow1

JOIN(COLLECT({Subjects - VLookup Range 3}, {Subjects - VLookup Range 2}, [LMS Subjects]@row <> ""), "; ")

This is the correct setup. Because your criteria is everything that is NOT BLANK, I would assume that all values would be returned.

You will need to change the criteria range and criteria.

I do not recommend using multi value cells as a lookup criteria. I haven't tried it in a couple years, but I was coming across the issue that the sheet I was referencing needed to have the same combination of values as the cell I used for the lookup criteria.

Also, some of your values are 1 value, but I believe you are trying to reference them individually.