Join Collect Simplified

Andrea Westrich

Can anyone explain the Join Collect formula in plain language?

I have two sheets, CS Master and Client Master.

In the Client master I want to link the Options column from CS Master and match the Address column. Can anyone simplify the formula for me in terms of English, meaning can you just use words to tell me what goes where. This is an example of something I did for Index/Match:

=INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellincurrentsheetThatHaveTheValueToMatch@row;{ColumnothersheetWithTheValueToMatchAgainsTheCell}; 0))

I have a hard time know what goes where even with the help. Thanks

Lucas Rayala

Hi @Andrea Westrich, you only want to use the JOIN function if you want to bring information from multiple rows into one cell. If you are trying to narrow down your search to one cell, I would use INDEX COLLECT.

=INDEX(COLLECT({Column you want to collect information from}, {Column with the data you are using as your lookup data}, [Column with your lookup data]@row), 1)

The INDEX function is used to narrow a search down to a specific column and row; essentially, INDEX(column name, row number). However, the COLLECT function (generally) narrows down your result to a single piece of information, so you put a "1" for the row, because you want the first (and only) row of information.

INDEX Function | Smartsheet Learning Center