Lookup and join 3 columns from another sheet

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!)

Answers

  • Paul Newcome
    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
    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))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • 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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!