Lookup and join 3 columns from another sheet

msahin

Hi,
during my project, I need to lookup and take data from 3 different columns which are located in another sheet.
I tried some formulas with Index+collect, Index+Collect+Join etc. but none of these worked.
General question: Is it possible to do this? I didn't notice anything here in the community.

to visualize it:
this is the source file with an matching "ID" and 3 columns.

and this is the result I want. The formula should lookup of ID column, and join the results in the same column. (different sheets!)

Paul Newcome

The most straightforward would be to write three separate INDEX/MATCH formulas (one for each field to be brought over and "add" them together with the commas.

=INDEX/MATCH + ", " + INDEX/MATCH + ", " + INDEX/MATCH

The other easier option would be to join them together into a single column on the source sheet and then use an INDEX/MATCH to bring this joined column over.

Mark.poole

@msahin

=Index({Column2 ref},Match([Primary Column]@row,{Primary Column Ref},0))+", "+Index({Column3 ref},Match([Primary Column]@row,{Primary Column Ref},0))+", "+Index({Column4 ref},Match([Primary Column]@row,{Primary Column Ref},0))

msahin

thank you both.
This solution is giving me results with commas even when some columns are empty (expl: Person1, , Person3)
and I did not want this happen.
I managed it with "if, if true, if false) codes.. but the code is too big any wanted to know if there is another way to get these unnecessary commas out of the code, without if coding.

Paul Newcome

You would need a JOIN/COLLECT in a helper column on the source sheet to collect the cells that are not blank and then join them together.

=JOIN(COLLECT([Column 1]@row:[Column 3]@row, [Column 1]@row:[Column 3]@row, @cell <> ""), ", ")

Then you would use an INDEX/MATCH or INDEX/COLLECT to bring the helper column over based on the ID.