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

user0
  • 27,169
  • 10
  • 55
  • 81
Bardo
  • 317
  • 1
  • 4
  • 13

2 Answers2

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

Rubén - Volunteer Moderator -
  • 46,305
  • 18
  • 101
  • 297
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

user0
  • 27,169
  • 10
  • 55
  • 81