google sheets query function where A matches string in a cell

Question

I am trying to use a cell value to impact a query. The issue seems to be that the cell value is a string which I am unable to use as a dynamic object in the query.

I have based my findings and examples below on the example file built into google sheets for QUERY and the section "select where"

filtering with the term 'where' in Query dynamically fails but does not if either

1. the cell contains a number (as in googles own example )
2. the string value is written as eg 'Dana' instead of "&A10&" which has the string 'Dana'

example code in cell say A11 0. does not work, what I would like to do: =QUERY($A$1:$H$8, "select A where (B<>'Eng' and G=true) or (A matches "&A10&")")

1. works - 1. the cell is a number (as in the google example) =QUERY($A$1:$H$8, "select A where (B<>'Eng' and G=true) or (D matches "&A10&")") this results in an error saying column 'Dana' is not found
2. works - 2. the string value is written as eg 'Dana' instead of "&A10&" which has the string "Dana" =QUERY($A$1:$H$8, "select A where (B<>'Eng' and G=true) or (D > 'Dana')") interestingly what also returns a query but ignores the actual value in A10 is: =QUERY($A$1:$H$8, "select A where (B<>'Eng' and G=true) or A matches "&CHAR(34)&"&"&char(64+value(CELL("col",A11)))&CELL("row",A11)&"&"&CHAR(34))

my best guess is that something is going odd based on using '' or "" on the google sheets side due to the error but I really do not know.

-- Does anyone know of any work around ideas?

My hope is to use this on a large number of rows to return a single value in a different and more complicated query but so far I am stuck to do it without the QUERY function which I thought would be more elegant.

Answer 1

You should add ' ' Try this!

=QUERY($A$1:$H$8, "select A where (B<>'Eng' and G=true) or (A matches '"&A10&"'")

That's how it takes your given cell as a string ;)