Match table with database based on input and return multiple rows

I'll try to explain this the best i can.

I've got a database that hardly ever changes on 1 tab and i've got a dynamic table on other tab which is generated with data from the web.

I am trying to set up a formula that changes the outcome based on the colors i selected. i can't link the original sheet but i tried to put together a small test sheet to make it more clear

CodePudding user response：

Another option it stacks up different queries with REDUCE, also gives you a message if product is not found in that colour:

=REDUCE({A2:B2,"colour"},SEQUENCE(COUNTA(E3:E)),LAMBDA(a,v,{a; IFERROR (QUERY(A3:B100,"SELECT '"&INDEX(D3:D,v)&"',A,B where A = '"&INDEX(E3:E,v)&"' and B matches '"&JOIN("|",FILTER(G3:G,G3:G<>""))&"' label '"&INDEX(D3:D,v)&"' ''",),{INDEX(D3:D,v),INDEX(E3:E,v),"not found in any colour"})}))