Why is a CONTAINS() workaround working in a JOIN(COLLECT()) function?
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!
Help Article Resources
Check out the Formula Handbook template!