Finding multiple cells from a previous column

[Steve Morris]

The goal is to find all of the employees from the first column that are associated with a city once a city is entered in a cell below the original chart. This example manually shows all of the employees associated with Memphis from the first two columns. I want this to show them in descending order as the city is changed. The spreadsheet is enclosed along with the specific problem definition in the first tab.

	5/6/2022	5/13/2022
Employee 1	Memphis	Memphis
Employee 2	Nashville	Nashville
Employee 3	Local	Knoxville
Employee 4	Nashville	Nashville
Employee 5	Nashville	Local
Employee 6	Knoxville	Atlanta
Employee 7	Local	Local
Employee 8	Atlanta	Knoxville
Employee 9	Chattanooga	Chattanooga
Employee 10	Chattanooga	Local
Employee 11	Chattanooga	Chattanooga
Employee 12	Memphis	Atlanta
Employee 13	Memphis	Chattanooga
Employee 14	Memphis	Knoxville
Employee 15	Atlanta	Atlanta
City	Date	Date
Memphis	5/20/2022	5/7/2022
	Employee	Employee
	Employee 1	Employee 1
	Employee 12
	Employee 13
	Employee 14

 

[AlanSidman]

using power query, I was able to achieve what you are looking for, but with a slightly different presentation. I created a parameter query. Change the value in the combo box at A19. Click on Data-->Refresh All and your result will update and present starting in B31. You can examine the Power query by clicking on Data-->Queries and Connections -->Edit in the attached.

 

[p45cal]

In cell B22:

=FILTER($A$2:$A$16,INDEX($B$2:$G$16,0,MATCH(B$19,$B$1:$G$1,0))=$A$19,"None")

copy across to E22. No copying down! Make sure there's plenty of space below the formula for the results to spill down to.

 

[Steve Morris]

Thanks...that worked great!

 

[Steve Morris]

I'm following all of the logic of the formula but I am getting an error when moving it to columns F and G.

 

[p45cal]

That's right, but there has to be a date in cell F19 which matches one of the dates in B1:G1.

 

[Steve Morris]

Thanks!