JOIN(COLLECT) for multiple values formula is pulling all values in reference sheet
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 "; ".
Answers
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.4K Get Help
- 447 Global Discussions
- 144 Industry Talk
- 478 Announcements
- 5K Ideas & Feature Requests
- 85 Brandfolder
- 151 Just for fun
- 72 Community Job Board
- 490 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 302 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!