Google sheets - Query - Select where it contains more than one result

Question

I have a spreadsheet with several names of drugs, such as "amoxicillin". And, on the other tab are the drugs, only with their association "amoxicillin + potassium clavulanate".

I need the spreadsheet to return all fields where the drug is "mentioned" in tab 2. Something like: "Return everything that has the word "amoxicillin", similar to searching with Ctrl + F.

However, I would like the results to be in the same column. And I did it by query: ​

=IF(A:A<>"";query('Page2'!A:A;"Select * WHERE A CONTAINS '"&A2&"' ");"Not found") ​

The problem is that the CONTAINS '"&A2&"'​ ​only returns the data referring to this line, but I needed it to be repeated in the A3 and A4 lines without me having to change the query. Making all the results structured in the same column.

If possible to do this by query, it would be even better if you could group it this way:

Column A	Column B
Amoxicillin	Amoxicillin Esomeprazole + Clarithromycin
Amoxicillin	Amoxicillin + Potassium Clavulanate
Azithromycin	Azithromycin

Answer 1

You may try:

=reduce({'Pag1'!A1\'Pag2'!A1};tocol('Pag1'!A2:A;1);lambda(a;c;vstack(a;
       let(Σ;filter('Pag2'!A:A;search(c;'Pag2'!A:A));hstack(wrapcols(c;counta(Σ);c);Σ)))))