Multiple values in same cell return multiple text values from another sheet
I have a column that will contain multiple text input values. I would like the next column over to reference another sheet, find those values, and return the text value from another column on that reference sheet.
EXAMPLE:
Sheet 1, Column "ID Numbers" has the numbers A-123, B-234, and C-345 in the same row.
Sheet 2, Column "ID Number" has values A-123, B-234, and C-345 in different rows. In these same rows, Column "Name" has values "Item A", "Item B", and "Item C" respectively.
I would like Sheet 1, Column "Names" to search Sheet 2, Column "ID Number" for any number that appears in Sheet 1, Column "ID Numbers", and return the corresponding Sheet 2, Column "Name" for each number, all into the same cell/row in Sheet 1, Column "Names".
Is there a formula I can put in Sheet 1, Column "Names" that will do this?
Answers
-
It sounds like you need a JOIN/COLLECT. Are you able to provide some screenshots for reference?
-
I can, but I have to block out some info.
This would be "Sheet 1" in my example.
This would be "Sheet 2"
There's only one number in the second screenshot that is also in Sheet 1, but you get the idea.
-
Ok. You are going to want to use something like this:
=JOIN(COLLECT({Item Name}, {Element ID}, HAS([Elements in Batch - SEL #]@row, @cell)), CHAR(10))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!