Return multiple results based on a variable cell.

[Surferol]

Hi,
I want to use the value of a drop down box (already setup) to produce a list of identifiers from another sheet.
In the 2 screenshots, the yellow box of the timeline tab will be a drop down box, this will be the responsible units.
I want to select a responsible unit and all the ID names associated that unit is responsible for will automatically fill in in column B.

Thank you in advance,

 

[Fluff]

Maybe something like

=FILTER(Sheet1!A2:C1000, Sheet1!Z2:Z1000=A5)

 

[Surferol]

Maybe something like

=FILTER(Sheet1!A2:C1000, Sheet1!Z2:Z1000=A5)

Thank you Fluff,
That cam up with #Spill

 

[Fluff]

Just put it in one cell, and clear all the other cells.

 

[Surferol]

Thank you Fluff,
That cam up with #Spill

Hi again Fluff,
So I removed the cells with other info and the spill worked perfectly.
However, I only want to return one result not all the other data associated with the responsible unit. Only the ID

 

[Fluff]

Just change the ranges to suit.

 

[Surferol]

Hi again Fluff,
So I removed the cells with other info and the spill worked perfectly.
However, I only want to return one result not all the other data associated with the responsible unit. Only the ID

I got it, I just told it to search the one column and it is doing what I want.

Thank you once again for your help.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Surferol]

You're welcome & thanks for the feedback.

Hi Fluff,
I want to do a bit more with this. I've made the yellow box selectable for different options. Any of the options will return the results associated with the selected option.

If I make an option for "All" what can I do to return all results from the index?

 

[Fluff]

How about

=IF(A1="All",INDEX(CLSDIndex[ID],),FILTER(...