Multiple values in same cell return multiple text values from another sheet

leslie.g

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?

Paul Newcome

It sounds like you need a JOIN/COLLECT. Are you able to provide some screenshots for reference?

leslie.g

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.

Paul Newcome

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