Google Sheets Query Search Box | Search criteria

user_18302380 0
Viewed 102

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

google-sheets-formula google-sheets
2 Answers

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

Sheet modification:

Modified

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.
user_161186860

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.

user_75020290
Related