1

I am trying to make a search box in google sheets which searches data based on keywords. This is specific to a spare parts inventory system. Each item is defined by 5 columns namely Type, Part Name, Part Number, Price, Stock. One example of an item would be "Lockstitch","Needle Bar Bush","229-10312 / 331-2312", "500", "3000".

In a separate sheet, I am trying to make a search box where I put in keywords like "Lockstich" "Bush" and the result should display.

I have tried concatenating the column name into one string and concatenating the keywords and matching them using QUERY function, but the problem that arises is that when I try to search "lockstitch bush" it doesn't give result because it does not contain in the string "Lockstitch Needle Bar Bush 229-10312 / 331-2312 500 3000"

I want the code to separately search each keyword in the combined string and give out results matching the keywords

player0
  • 124,011
  • 12
  • 67
  • 124
Mansimar Singh
  • 45
  • 6
  • Check this answer as well: https://stackoverflow.com/questions/58267354/manual-data-tagging-and-lookup/58269256#58269256 – a-burge Oct 11 '19 at 09:14

1 Answers1

3

you can try like this:

=FILTER(A2:E6, REGEXMATCH(TRANSPOSE(QUERY(TRANSPOSE(LOWER(A2:E6)),,999^99)), 
 TEXTJOIN("|", 1, SPLIT(LOWER(H1), " "))))

0

or more strict:

=ARRAYFORMULA(QUERY({A2:E6, TRANSPOSE(QUERY(TRANSPOSE(A2:E6),,999^99))}, 
 "select Col1,Col2,Col3,Col4,Col5 
  where "&TEXTJOIN(" and ", 1, IF(TRANSPOSE(SPLIT(H1, " "))<>"", 
 "lower(Col6) contains '"&TRANSPOSE(SPLIT(LOWER(H1), " "))&"'", ))&"", 0))

0

player0
  • 124,011
  • 12
  • 67
  • 124