How can I return an array of results from one worksheet based on criteria from another worksheet?

I hope the title of this makes sense. I’ve been trying to figure the best way to work what I am trying to accomplish.

I have a worksheet with a validation list of dates. I am trying to return all of the rows from a table, based on the date, but the date is in a second table.

Here is a mockup of what I am trying to accomplish:

Cell F2 is a validation list of dates.

The table Install Schedule has all of the site numbers, with the dates that devices are being installed.

Table Computer List is a list of computers by site number.

I would like to return a list of computers by the date they are being installed. I figure that I need to use a LET statement, but I’ve been having trouble figuring out the correct formulas. Using the following formula, I can get a list of the sites by date:

=FILTER(Schedule[Site Number],Schedule[Install Date]=SelectedDate)

That returns a list of sites by date. But I’m not sure how to use that to return the list of computers by site.

Thanks

>Solution :

Try using the following formula:

• Formula used in cell E6

=FILTER(Computers,1-ISNA(XMATCH(Computers[Site Number],
 FILTER(Schedule[Site Number],F2=Schedule[Install Date]))))

Where:

• Computers –> Table Starts from I16:J38
• Schedule –> Table Starts from I3:K12