I'm using Google Sheets as the backend for a no-code dev environment (Glide). I am trying to query an array rather than a single column.

Here's my formula that works for looking in just one column (L):

=Query(All_DeVS, "select C where L Contains '"& L27 &"' ",0)

*All_Devs is a named range (A1:R25).

Please don't be tripped up by the stuff in quotes around L27 - that's just passing a cell reference into the query (works great!).

The challenge has been to replace "L" with a range or an array. Can the WHERE point to more than one COLUMN? I've tried using a named range. I've tried WHERE * and I've tried a few other silly things.

HLOOKUP and VLOOKUP don't work for me in this scenario because I'm searching across both H and V in unsorted data. I guess I could transpose all columns into a single row (or all rows into a single column) on another sheet and do the appropriate H/V LOOKUP there. But that seems klugey.

  • Welcome. Please read how to and share a test sheet so as you can be easier helped. – marikamitsos Jan 6 at 19:37
  • Keep in mind that we don't know your end goal, nor what your data looks like, nor your layout. So while your post may seem perfectly clear to you (who does know your end goal and can see your data and layout), it is next to gibberish for those of us who don't know those things. Please consider sharing a link to your spreadsheet (or a copy of it, or a true representative sample from it), being sure to set the link's Share permission to "Anyone with the link > Editor." – Erik Tyler Jan 7 at 6:34

Your Answer

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

Browse other questions tagged or ask your own question.