Making a query formula search box with partial match

1
votes

Thanks in advance to all, I have a spreadsheet with multiple sheets. I want my master sheets to have a search box based on a query that is referencing from a cell( my case A1)

*iv managed to accomplish this, but the search results are only exact match. can someone help please how can make it a partial match instead of exact or even combined the two.

**iv tried this thread but it doesn't work, maybe I'm doing something wrong thanks:

Exact result in Google Query, followed by partial match if exact result does not exist

  • this is the query that is working right now

    =QUERY({sheet1!A2:I24;'sheet2'!A2:I26;'sheet3'!A2:I26},"select Col1, Col2, Col4,Col7,Col9 where Col1 = '"&A1&"'",1)

Cheers to all,

1
google-sheetsformula

1 Answers

1
votes

From the reference:

like - A text search that supports two wildcards: %, which matches zero or more characters of any kind, and _ (underscore), which matches any one character. This is similar to the SQL LIKE operator. Example: where name like fre% matches 'fre', 'fred', and 'freddy'

where Col1 like '%"&A1&"%'"

Another, more powerful approach is to use filter + regexmatch:

=filter({sheet1!A2:I24;'sheet2'!A2:I26;'sheet3'!A2:I26}, regexmatch({sheet1!A2:A24;'sheet2'!A2:A26;'sheet3'!A2:A26}, A1))

See more on regex syntax here.

The result of a filter may be a query data source:

=query(filter(..., ...), "select Col1, ...")