so basically I have a searchbox in my sheet that searches and pulls up data. For a reference see this image:

Formula:

=QUERY({'Pallets & Locaties'!A2:G;Voorraadverschillen!A2:G}, "SELECT * WHERE "&TEXTJOIN(" AND ", TRUE, ARRAYFORMULA(IF(ISBLANK(A4:A10), "", A4:A10&" "&B4:B10&" '"&C4:C10&"'"))))

Test Sheet

Note:

• The above formula will allow you to search on other columns with their own words and criteria to search.
• Only rows with Kolom values will be included in the criteria. If you only need Col1 criteria, make sure to leave other rows blank.
• This does use an AND search, meaning all of the criteria should be true and match the row. Feel free to use OR in the TEXTJOIN function if you only want to search all rows matching any of the criteria.
• This will only search on sheets Pallets & Locaties and Voorraadverschillen. Add the necessary sheets if you need them.

CodePudding user response：

EDIT:

Cleaned up the formula to not be so repetitive.

=IF(A4<>"",(QUERY({'Pallets & Locaties'!A2:G;Voorraadverschillen!A2:G},"Select * WHERE "&textjoin(" OR ", true, arrayformula("Col"&ROW(1:7)&" "&B4&" '"&A4&"'")))),(QUERY({'Pallets & Locaties'!A2:G;Voorraadverschillen!A2:G},"Select * WHERE Col1 IS NOT NULL")))

This searches every column for the data, as long as data is not identical in two columns you won't have issues. An example would be the search criteria "MELD" being in both Column A and B. If that were the case, only the results from the first matching column would populate.