Google Sheets Query Search Box | Search criteria

Question

so basically I have a searchbox in my sheet that searches and pulls up data. For a reference see this image: https://i.imgur.com/MVTUCSw.png. So basically in cell A4 I put the data that I am looking for, but my formula restricts me to only looking up stuff in 1 row. For example, data starting with the word MELD, but I would like to be able to also look up data based on for example the someone their name.

The formula I use for the searchbox: =QUERY({'Pallets & Locaties'!A2:G;Voorraadverschillen!A2:G}, "SELECT * WHERE Col1 "&Opzoeken!B4&" '"&A4&"'")

The data that I want to be able to look up is stored in 2 sheets: Pallets & Locaties - https://i.imgur.com/qV7h2tz.png and in Voorraadverschillen - https://i.imgur.com/foqLkKa.png.

The searchbox is only able to lookup data in row, but I just want to be able to search for any kind of stored data in any of the sheets.

Here is my sheet for reference: https://docs.google.com/spreadsheets/d/10wmnxV16JUiD_b_54abkiLPwTqLITWRcUw3gsqvXoBE/edit?usp=sharing

Answer 1

I'd recommend you add more rows for the lookup criteria and add a column for what column it would search for.

Sheet modification:

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.