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:
CodePudding user response：
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:
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: