5

I am trying to query five ranges with the results displayed across a single row. To do this, I was using { } with commas separating each range, but it's only giving me the first range in return.

Formula:

=QUERY({A2:C2,D2:F2,G2:I2,J2:L2,M2:O2}, "select Col1, Col2, Col3 where Col3='USA'")

Sample spreadsheet

|improve this question|||||
2

We need to use five QUERY functions, one for each set of columns, but then add something else to remove the blank columns. The following formula use JOIN and SPLIT:

=SPLIT(JOIN(",",IFERROR({QUERY({A2:C2},"select Col1, Col2, Col3 where Col3='USA'"),QUERY({D2:F2},"select Col1, Col2, Col3 where Col3='USA'"),QUERY({G2:I2},"select Col1, Col2, Col3 where Col3='USA'"),QUERY({J2:L2},"select Col1, Col2, Col3 where Col3='USA'"),QUERY({M2:O2}, "select Col1, Col2, Col3 where Col3='USA'")},"")),",")

Original answer (keeped here because this answer got an upvote before the OP comment)

The comma separator (or slash if the spreadsheet use a comma as decimal separator) is used as column separators. Replace the comma by semicolon.

=QUERY({A2:C2;D2:F2;G2:I2;J2:L2;M2:O2}, "select Col1, Col2, Col3 where Col3='USA'")

|improve this answer|||||
  • I was hoping to keep the results in the same row, which is why I was thinking the commas would work. How can I keep it in one row? – Bardo Oct 30 '17 at 2:12
  • Now I understand. We need to use five QUERY functions, one for each set of columns, but then add something else to remove the blank columns. – Rubén Oct 30 '17 at 2:15
0

=ARRAYFORMULA(SUBSTITUTE(SPLIT(JOIN(" ",TRANSPOSE(QUERY(TRANSPOSE(QUERY(SUBSTITUTE(
 {A2:C2;D2:F2;G2:I2;J2:L2;M2:O2}," ","♦"),"where Col3='USA'")),,50000)))," "),"♦"," "))

0

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.