Google Sheets Query() Function Returning Unexpected Data

0
votes

I am having trouble with the QUERY() function in Google Sheets. I'm not certain I am using the correct function to generate the required data. I am far more comfortable with T-SQL, but my wife needs a mailing list generated from her current google sheets.

The facts:

  • Sheet2 is populated with data from B3:K150

  • I have a list of names in Sheet2, Column B.

  • I have a list of addresses in Sheet2, Column G

  • Sheet1 contains no data other than the data populated in Column B (See below)

  • Sheet1, Column B is populated with a unique list of names from Sheet2, Column B using =Unique('Sheet2'!$B$3:$B$150).

Goal:

  • I would like to populate Sheet1, Column C with Sheet2, Column G WHERE Sheet1(ColumnB) = Sheet2(ColumnB)

Obviously, using SQL, we would write this as such:

SELECT [Sheet1].[ColumnB]
      ,[Sheet1].[ColumnC] 
FROM [Sheet1] 
    INNER JOIN [Sheet2] 
        ON [Sheet1].[ColumnB] = [Sheet2].[ColumnB]

What I have tried:

INSERT the following formula into Sheet1!C3:

=QUERY(Sheet2!B3:K150,"SELECT G WHERE B ='"&Sheet1!B3:B150&"'",0)

Sheet screenshots:

enter image description here enter image description here

What am I missing?

2
sqlgoogle-sheetsgoogle-query-languagegoogle-sheets-query

2 Answers

1
votes

This worked well for me: 

=unique(query(Sheet2!B3:G150,"select B,G"))

as posted by user: pnuts in comments above.

0
votes

Try this in Sheet1C3 =Arrayformula(if(B3:B=Sheet2!B3:B,Sheet2!G3:G,"")) Let me know if you need me to explain the formula.