Error in Google Spreadsheet Inner Queries

Question

I have the following table in Google Spreadsheet. I want to select items from column B but based on a condition that only these rows are selected where the values of columns A and D match.

I constructed the following query to check that a syntax is correct:

=CONCATENATE("Select B where A matches '.*"&TEXTJOIN(".*|.*"; true; query(D2:D; "Select D"));".*'")

Executing this gives the following result:

Select B where A matches '.*Item1.*|.*Item2.*|.*Item3.*'

When I now use this syntax in a query, then the result is correct: 1 2 3

=query(A2:B; "Select B where A matches '.*Item1.*|.*Item2.*|.*Item3.*'")

But when putting all this together I got the following error message. Why is that?

=query(A2:B; "Select B where A matches '.*"&TEXTJOIN(".*|.*"; true; query(D2:D; "Select D"));".*'")

question from:https://stackoverflow.com/questions/65598780/error-in-google-spreadsheet-inner-queries

Answer 1

You put an ; instead of & at the end:

=query(A2:B; "Select B where A matches '.*"&TEXTJOIN(".*|.*"; true; query(D2:D; "Select D"))&".*'")

Explanation:

This ".*'" was passed as a third argument. But the third argument (which is optional) needs to be of type number.

See the official documentation for more details: