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
-
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.
-
=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. -
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!