CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-query-language
Jane Mänd
Jane Mänd

Reputation: 187

Error in Google Spreadsheet Inner Queries

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.

enter image description here

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"));".*'")

enter image description here

Upvotes: 0

Views: 52

Answers (2)

player0
player0

Reputation: 1

unless you somehow query down the D column you don't need to use query on D column because textjoin will remove blanks. and the culprit in your formula was the last semicolon ;".*'" where it should be &".*'"

try:

=QUERY(A2:B; 
 "select B 
  where A matches '.*"&TEXTJOIN(".*|.*"; 1; D2:D)&".*'"; 0)

Upvotes: 1

Marios
Marios

Reputation: 27350

Solution:

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:

enter image description here

Upvotes: 1

Related Questions