Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • Community Champion

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions