Google Sheets Join information from two pages with query & vlookup

user_3117283 0
Viewed 59

I know this has been asked several times, but I just can't seem to understand how to write the formula and I'm hoping to get some help. Consider the following (example data) sheet: https://docs.google.com/spreadsheets/d/1t_I_stZmZea4sfGPsCu6GtBhGJJT16CZ-sEu7JubFKc/edit?usp=sharing

First, note that I am importing data on "API Data" utilizing importJSON().

My goal is to combine (join) data from two sheets. I need "dataseries cloudcover" from 'API data' and "Dataseries example,Dataseries example 1,Dataseries example 2" from 'join'.

I gave it a shot here:

=query('API data'!A:L,"Select " & vlookup(B:B,'API data'!B:L,3,FALSE) & ",B,C,D,E,F,G,H,I,J,K,L")

Here is a SS of what I would like to see enter image description here

google-sheets-formula google-apps-script google-sheets
1 Answers

This formula can help you to get that data:

Note: Just add the formula in A2

={ARRAYFORMULA(IF(ISBLANK('API data'!C2:C),"",ARRAYFORMULA(VLOOKUP('API data'!C2:C,'API data'!C2:D25,2)))),ARRAYFORMULA(IF(ISBLANK(Join!A2:A),"",ARRAYFORMULA(VLOOKUP(Join!A2:A,Join!A2:D25,{2,3,4},FALSE))))}

And it will look like this: enter image description here

Edit:

Editing and adding more information about the use of this formula.

The formula is constructed with 2 different VLookUps, 1 for each tab, and they are merged using:

={First Array, Second Array}

The first Array is:

ARRAYFORMULA(IF(ISBLANK('API data'!K2:K),"",ARRAYFORMULA(VLOOKUP('API data'!K2:K,'API data'!K2:L25,2))))

The second Array is:

ARRAYFORMULA(IF(ISBLANK(Join!I2:I),"",ARRAYFORMULA(VLOOKUP(Join!I2:I,Join!I2:L25,{2,3,4},FALSE))))

The core part of the first array for this formula is:

ARRAYFORMULA(VLOOKUP('API data'!K2:K,'API data'!K2:L25,2))

The IF(IsBlank(column,"",Vlookup) will remove any empty value of the Array.

The same thing with the second Array, with the difference that I use an Array {2,3,4} to call all the columns in the second sheet.

Reference:

user_173901450
Related