Why is a CONTAINS() workaround working in a JOIN(COLLECT()) function?

Torsten Rich-Wimmer

Hi all,

I stumbled upon a solution for a complex request I received today, but I found it by accident and I don't fully understand why it is working. The code is:

=JOIN(COLLECT(B:B, A:A, CONTAINS(@cell, C@row)), CHAR(10))

I want the formula to output an entry in (B) that corresponds with (A) based on the numeric code displayed as the first several characters of each string in (C). Oh, and the user must be able to select several entries in (C). (In function, A & B live in an entirely separate database from C & D; I created this example for simplified visualization.) Easy peasy, right!?

As I understand it, COLLECT gathers data from B:B if A:A contains "Criterion1" in the third argument. If I input C@row as Criterion1, the formula outputs nothing since the code is only the first 4 characters.

=JOIN(COLLECT(B:B, A:A, C@row), CHAR(10))

If I input "1111" into a cell in column C, Output would display "LN1". Hooray!

However, I am unable to select "1111" and "2222" at the same time, nor am I able to select "1111 | LongName 1". Obvious, I know.

However, if I change 'C@row' to use the CONTAINS function first mentioned, both above examples work. In practice, it feels like it reverses the role of the third (criterion1) argument so that it assesses row C based on criteria from row A. Why? Since CONTAINS returns a boolean, why does this tell the COLLECT function to return the corresponding entry in column (B)? And, based on the fact that the "CHAR(10)" at the end splits the multi-select into separate entries, I assume that it is the COLLECT function splitting these into separate entries. Does my use of the CONTAINS function instruct the COLLECT function to run recursively?

Thank you for your input!

Gia Thinh

Hi Torsten,

Something needs to make clear with functions.The output of COLLECT function is an array of items, and it must be used within another function (not standalone), in this case is JOIN function.

The JOIN fuction helps to concatenate items of the COLLECT function with the CHAR(10) (ENTER charactor) into a string. So this JOIN is used just for showing up the output of COLLECT so that we can see the result of COLLECT.

Let's analys this statement : COLLECT(B:B, A:A, CONTAINS(@cell, C@row))

It means : Help me to COLLECT all items in B collumn that match this condition : items in A collumn (@cell) are existed in C@row

Hope this makes clear for your question.

Gia Thinh

Hi Torsten,

Something needs to make clear with functions.The output of COLLECT function is an array of items, and it must be used within another function (not standalone), in this case is JOIN function.

The JOIN fuction helps to concatenate items of the COLLECT function with the CHAR(10) (ENTER charactor) into a string. So this JOIN is used just for showing up the output of COLLECT so that we can see the result of COLLECT.

Let's analys this statement : COLLECT(B:B, A:A, CONTAINS(@cell, C@row))

It means : Help me to COLLECT all items in B collumn that match this condition : items in A collumn (@cell) are existed in C@row

Hope this makes clear for your question.

Torsten Rich-Wimmer

Thank you @Gia Thinh! You said "array," and everything fell into place in my mind!

=JOIN(COLLECT(B:B, A:A, CONTAINS(@cell, C@row)), CHAR(10))

If I understand correctly (using the example above), 'COLLECT' evaluates column A to then store corresponding items from column B into an array based on criteria from the third argument, which, in this case, returns TRUE or FALSE via a 'CONTAINS' function. As the COLLECT function moves from cell to cell to assess each in Column A, the CONTAINS function returns true if the corresponding cell in column C (C@row) contains at least one instance of data found within the 'A' cell being assessed. When 'TRUE', the corresponding entry in column B is added to the array, and the COLLECT function moves on to evaluate the next cell. Finally, 'JOIN' converts the array created by 'COLLECT' into a string.

So, if I were to change the formula to =JOIN(COLLECT(B:B, A:A, ISODD(@cell)), ", ") , the output would be: "LN1, LN3, LN5, LN7" (assuming that column A is not multi-select, but that's another array-related can of worms).

Can't wait to experiment with other uses now that I have a better understanding.

Thank you again!!

Gia Thinh

Yes, play around with functions is a way to enhance our understanding. Enjoy it.