Index/Join/Collect - Question

Zach L

Hello,

I have two sheets I am trying to reference together and I am having a bit of trouble.

I have one cell in a sheet with row numbers (i.e. 1,2,3,4,5) and another sheet that based on those numbers gives a control. I am trying to take the cell with the list of numbers and instead simply display the control name so we don't have to jump between two sheets. I can make INDEX work to grab one but cant for the life of me use a function to go through all the numbers and grab their associated controls.

Example of the control list

Example of main sheet

I would love to be able to add a list of numbers to the above cell (15, 16, 17) and instead have it show the control names (Master Keys, Redundant Components, Software repository, etc.)

Thank you!

Z

Paul Newcome

You would need a JOIN/COLLECT combo instead of an INDEX function, and you would need to make the column you are putting numbers into a multi-select dropdown type column.

Zach L

Apologize, still a bit new to this whole side. Still running stuck, Now I am getting a blank cell.

Thanks a ton Paul.

Paul Newcome

You will also need a HAS function for [Column3].

=JOIN(COLLECT({Range 1}, {Range 3}, HAS([Column3]@row, @cell)), " - ")