Google sheet query across multiple sheets

Question

I have a google sheet with 4 tabs called

hoses intercooler pipeworkCNC others

i am trying to search all sheets returning if anything searched for is found in column A

I have tried the following

=QUERY({hoses!A2:G3000; intercooler!A2:G3000; pipeworkCNC!A2:G3000; Others!A2:G3000},"select * where (lower(A) contains lower('"&B2&"'))")

but it returns error saying NO COLUMN A

I know i have to reference the sheet and column in the select * where (lower(A) part of query i was thinking along the lines of

select * where (lower(hoses!A) or (lower(intercooler!A) etc but this does not work

Can anyone help

Thanks

doubleunary · Accepted Answer · 2022-05-27 09:12:47Z

1

Your data is not a range but an { array expression }, so you will have to use Col1, Col2, Col3 instead of A, B, C, like this:

=query( 
  { 
    hoses!A1:G; 
    intercooler!A2:G; 
    pipeworkCNC!A2:G; 
    Others!A2:G 
  },
  "where lower(Col1) contains '" & lower(B2) & "' ", 
  1 
)

answered May 27, 2022 at 9:12

doubleunary

21.9k13 gold badges38 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google sheet query across multiple sheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related