Google Sheets Query() Function Returning Unexpected Data

Question

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:

What am I missing?

THANOS--WAS--RIGHT · Accepted Answer · 2017-08-04 15:12:08Z

1

This worked well for me:

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

as posted by user: pnuts in comments above.

answered Aug 4, 2017 at 15:12

THANOS--WAS--RIGHT

831 silver badge8 bronze badges

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

Comments

mongoose36 · Accepted Answer · 2017-07-30 03:22:22Z

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.

Collectives™ on Stack Overflow

Google Sheets Query() Function Returning Unexpected Data

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related