I need to filter a range depending on the first cell of a column in Google Sheets

Question

My situation is the following:

I have a database of musicians in a file. That file has multiple tabs depending on the instrument with various informations: name, age, contacts, and most importantly the concert dates the musicians will attend.

Database example with only two instruments https://docs.google.com/spreadsheets/d/1wZsZiE2A_VdUmmNV9uSRmBMGQz0uw6GwAPuCsMa8PoI/edit?usp=sharing

You can see that columns D and E have dates in them, and under them a Y or N, depending on the musician's availability for that concert.

In another file (NOT the same one) I need to be able to import only the musicians that will play in certain dates. Example sheet: https://docs.google.com/spreadsheets/d/18rm4HQDh5Bx0sUbdFOQ_kUzf5Gu2vcpt2HMqphqKuqg/edit?usp=sharing

Here, there are multiple tabs for various concerts, and in each tab I need to import only the musicians that will play in the specified date (here it's set in B1).

Using FILTER() and IMPORTRANGE() doesn't work if I add dates (I want to be able to add new concerts to the left of the "old" concerts) to the database file it wrecks everything, because IMPORTRANGE() doesn't automatically updates changes made to the original file. I tried LOOKUP() and similar functions, but I'm not sure how to make them work in my situation. Any help is welcome!

Answer 1

there are many ways for example...

paste in A4:

=ARRAY_CONSTRAIN(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IMPORTRANGE(
 "1wZsZiE2A_VdUmmNV9uSRmBMGQz0uw6GwAPuCsMa8PoI"; A3&"!A1:E100")); 
 "where lower(Col1) matches 'name|email|age|"&TO_TEXT($B$1)&"'"; 0)); 
 "where Col4 = 'y'"; 1); 9^9; 3)

copy A4 and paste in A11: