7

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

1

2 Answers 2

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'")

2
  • 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, 2017 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. Oct 30, 2017 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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