How to make a Query to write cell value based on content of another spreadsheet on GoogleSheets?

Question

I've two spreadsheets and I want write the values of a column based on the values of another columns.

On the master spreadsheet I've two columns of interest: 'Ativo' and 'CNPJ'.

On the second spreadsheet called 'CNPJs' I've the two columns called 'TICKER' and 'CNPJ'.

My goal é automatically fill the column 'CNPJ' of master spreadsheet based on the condition 'Ativo' = 'TICKER' on the 'CNPJs' spreadsheet.

This is the master spreadsheet with the void column ready to be filled.

This is the CNPJs spreadsheet with the desired values of CNPJ field.

This is the query used and returning a error:

=QUERY(CNPJs!A2:B16; "select B2 where (CNPJs!A2 = A2)")

Answer 1

The query won't work as written.

It would usually be in this format:

=QUERY(CNPJs!A:B;"select B where A matches 'xxx' ";0)

but it won't evaluate down each row for xxx.

You're probably better with a VLOOKUP in an ARRAYFORMULA like this in cell B2 of the master sheet:

=arrayformula(iferror(vlookup(A2:A;CNPJs!A:B;2;false);))