Google-sheets – Query Multiple Ranges with Results in Row

concatenateformulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

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

Best Answer

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

Related Topic