CODEWITHSUNDEEP
google-sheets-formula
Nazneen Shaikh
Nazneen Shaikh

Reputation: 19

Google Sheets Query+Importrange with where clause referencing data column of the resultant sheet

So I have 2 google sheets(workbooks), A and B. B is a master with 20 column of information, 2 of which are firstname and lastname. In A i have only 2 columns filled firstname and lastname. I need to get data from 5 other column for each row in A.

I have the following query which works:

=Query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1mvDr1nki65j4KC3PKY8Cd_dxZFL7emkIHtK4CCMQwkk/edit#gid=664869195","Form Responses 1!A:K"),"select Col5, Col6, Col10, Col11 where Col2 contains 'John' and Col3 contains 'Doe'")

Replacing with cell value also works as below.

=Query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1mvDr1nki65j4KC3PKY8Cd_dxZFL7emkIHtK4CCMQwkk/edit#gid=664869195","Form Responses 1!A:K"),"select Col5, Col6, Col10, Col11 where Col2 contains '"&Housing!A2&"' and Col3 contains '"&Housing!B2&"'")

However when I try to make it generic it DOES NOT WORK.

=Query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1mvDr1nki65j4KC3PKY8Cd_dxZFL7emkIHtK4CCMQwkk/edit#gid=664869195","Form Responses 1!A:K"),"select Col5, Col6, Col10, Col11 where Col2 contains '"&Housing!A&"' and Col3 contains '"&Housing!B&"'")

Can someone please tell me how do I get this work?

Thank you in advance

Upvotes: 0

Views: 45

Answers (1)

horanimal
horanimal

Reputation: 434

Unfortunately I don’t think the query function is meant to be used in the way you are trying to use it :(

I would suggest splitting up the operation and looking into using some helper functions & XLOOKUP().

For example, you might:

  1. Have a separate tab where you use IMPORTRANGE() to import your data.
  2. Add an array formula to create a column on the far left (let’s say col A that joins the first & last names together to make a key (CAUTION: assumes no one has the same first & last name)
  3. In another tab, Grab all the unique names using using =UNIQUE(ImportportRangeTab!A:A)
  4. Use BYROW() and XLOOKUP() to grab all the columns of data from the imported range tab that correspond with the list of names

Upvotes: 0

Related Questions