Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-formula

Google Sheets Join information from two pages with query & vlookup

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

Solution

  • 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: