1

Let's say you have a table of properties in a Google Sheet...

Col A (Name)
Property 1
Property 2
Property 3
Property 4
Property 5

... and you want a formula-driven solution that pulls data on certain properties, specified by a comma-separated literal string like "Property 2,Property 5".

The query() function comes to mind, which uses mySQL syntax. I tried these WHERE queries:

  • SELECT A WHERE 'Property 2, Property 5' LIKE '%{$A}%' -- No error but returns empty set.
  • SELECT A WHERE INSTR('Property 2, Property 5', A) -- returned error: Unable to parse query string for function QUERY parameter 2: PARSE_ERROR: Encountered " 'INSTR'" at line 1, column 16. Was expecting one of: "("... "("...

Is there some other query to find the needle in the haystack, where the haystack is a literal string and the needle is a field in the query?

player0
  • 124,011
  • 12
  • 67
  • 124
Steven
  • 13
  • 3

1 Answers1

1

try:

=QUERY(A:A; "select A where A matches 'Property 2|Property 5'"; )
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Brilliant! I knew there had to be some way, and =substitute() can replace the commas with pipes. Thanks! – Steven Nov 29 '22 at 14:25