Join tables in google sheet - full join

Question

I have two tables in google spreadsheet. They have a common unique identifier (Account id). Now I need to join these tables into a third table containing all rows from both tables.

Please have a look at this sheet:

screen on spreadsheet

or follow the link to an example spreadsheet: https://docs.google.com/spreadsheets/d/17ka2tS5ysXqJnrpCxCTwNmCsTORPFP1Gatq4p1fPldA/edit?usp=sharing

I have manage to join the tables using this arrayformula:

=ARRAYFORMULA({G3:H8,VLOOKUP(G3:G8,{A3:A7,B3:C7},{2,3},false)})

But with this formula the joined table "misses" two rows:

20  N/A Klaus   Berlin
4   VW  David   Paris

The first missing row is found only in Table 1. The second missing has an ID that is found in Table 2 and has two (2) matching ID's in Table2, but only one row in the joined table

Is there a way to provide a formula that can handle this?

Ludvig, can you clarify something? Account ID 1, Anna, has two cars, Audi and Fiat. Account ID 4, David, has two cities (two addresses?), Stockholm and Paris. What if he had two cars as well? Would he have four records? Or which car would go with which city? I'm struggling to find something unique for each record in this collection of joined data... — kirkg13, Jun 29 '20 at 19:02
Yes that is correct @kirkg13. If David had two cara we would need 4 records. Maybe my data needs further preparation? But I see now that you have come up with a solution. — Ludvig Hansson, Jun 30 '20 at 06:28

score 2 · Accepted Answer · answered Jun 29 '20 at 20:37

2

previous answer is incorrect. use:

=ARRAYFORMULA(QUERY(UNIQUE(IFNA({G2:H10, 
 VLOOKUP(G2:G10, A2:C10, {2, 3}, 0);
 VLOOKUP({A3:A10; G3:G10}, {G3:H10; {A3:A10, IF(A3:A10, )}}, {1, 2}, 0), {B3:C10; 
 VLOOKUP(G3:G10, {A3:C10; {G3:G10, IF(G3:G10, ), IF(G3:G10, )}}, {2, 3}, 0)}})), 
 "where Col1 is not null order by Col1", 1))

answered Jun 29 '20 at 20:37

player0

124,011
12
67
124

That is quite an impressive formula @player0. Thank you so much. I need to look in to this. For starters I do not understand the IF-statements with empty return value, for example IF(G3:G10, ) – Ludvig Hansson Jun 30 '20 at 09:32
1

`IF(G3:G10, )` is same as you would use empty column `X3:X10`. the reason is to avoid `ARRAY_LITERAL` error which is caused when two matrixes (ranges) in curly brackets `{}` are of different size. example of error: `=INDEX({"a", "x";"y"})` – player0 Jun 30 '20 at 22:02

score -1 · Answer 2 · edited Mar 29 '21 at 13:27

Formula

=ArrayFormula(UNIQUE({
VLOOKUP({A3:A7;G3:G8},{G3:H8;{A3:A7,IF(A3:A7,)}},{1,2},FALSE),
{B3:C7;VLOOKUP(G3:G8,{A3:C7;{G3:G8,IF(G3:G8,),IF(G3:G8,)}},{2,3},FALSE)}
}))

Result

Explanation

{A3:A7;G3:G8} creates a list of all the account ids (it includes duplicates)
{G3:H8;{A3:A7,IF(A3:A7,)}} creates an array having three columns with values from Table 2 and blanks for ids from Table 1
{A3:C7;{G3:G8,IF(G3:G8,),IF(G3:G8,)}} creates an array having three columns with values from Table 1 and blanks for ids from Table 2
The first vlookup populates the first two columns (account id and car)
{B3:C7;VLOOKUP(G3:G8,{A3:C7;{G3:G8,IF(G3:G8,),IF(G3:G8,)}},{2,3},FALSE)} populates the third and fourth columns (name and city)
UNIQUE removes duplicates

Google spreadsheet "=QUERY" join() equivalent function?

Doesn't this omit several rows, Ruben? There should be two records for account ID = 1, for Anna of Stockholm, one for Audi and one for Fiat... — kirkg13, Jun 29 '20 at 17:53

Join tables in google sheet - full join

2 Answers2

Linked