Trouble with join/collect or index/match, with multiple columns and multiple results

Nick Amsler

Hello,

I can't seem to get this right and after too many hours on this, I am escalating it to the formula masters of this great community once again.

I am attempting to create a cell that will collect and join all the matched criteria from two different columns with the same criteria. There are multiple matches in both columns, and each time it matches I want it to grab the result from a third column and join all the results into this single cell. I have put the example of what I am trying to do below, screenshots, and the formula I am currently working with.

Current Formula: =JOIN(COLLECT({Sim Debrief Raw Data Range 1}, {Sim Debrief Raw Data Range 2}, HAS({Sim Debrief Raw Data Range 3}, [Primary Column]@row)), " ")

I have two sheets I am working on, one with the data I am trying to pull, and the new sheet gathering this data.

So in the transport preparation cell, I would like it to list every result when there is a match for this employee's number (the far left column) from the datasheet. However, the employee number will be listed multiple times and in two different columns on the same sheet.

So for the three instances found between the two columns, I would like the cell to pull and join the three results from the column range. I have tried many different ways of doing this and I am just stuck. Any help would be appreciated!

Paul Newcome

I would suggest a third column in the source sheet that combines the two employee id columns. A multi-select dropdown would be ideal.

Then you would insert a new text/number column on the target sheet and enter

=[Primary Column]@row + ""

plus quote quote

to ensure everything is in fact a text value. From there you would use a JOIN/COLLECT similar to

=JOIN(COLLECT({Source Sheet Range to Pull}, {Source Sheet Helper Column}, HAS(@cell, [Helper Column]@row)), " ")