How can I merge two tables in Google Sheets by ID like in SQL?-CodePudding

Suppose I have two tables, table1 and table2, like these:

table1

ID VALUE
0  2
1  8
4  3

table2

ID VALUE
1  9
3  5
0  1

How can I merge table1 and table2, such that the resulting table contains ID,VALUE1,VALUE2? Note: VALUE is different between table1 and table2. Also, the tables are unsorted and have different length, so there may be an ID in table1 that doesn't exist in table2 and vice-versa.

Resulting table

ID VALUE1 VALUE2
0  2      1
1  8      9

I have managed to select only the rows from table1 using this formula:

=INDEX(table1!$A$2:$B$4;MATCH(table2!$A2;table2!$A$2:$A$4;0))

In SQL, this could be easily done with:

SELECT table1.ID, table1.VALUE AS VALUE1, table2.VALUE AS VALUE2
FROM table1 
INNER JOIN table2 ON table1.ID = table2.ID

CodePudding user response：

Here's an approach you can use in sheets.

Table1: A:B

Table2: D:E

Output cell formula G2:

=BYROW(UNIQUE(SORT(FILTER(A2:A,MATCH(A2:A,D2:D,0)*(A2:A<>"")),1,1)),LAMBDA(ax,{ax,TRANSPOSE(FILTER({B:B;E:E},{A:A;D:D}=ax,{A:A;D:D}<>""))}))

CodePudding user response：

I found a way to do it with QUERY, though copying the data from table1 to another sheet, because I needed to keep table1 and table2 intact. Here's how to do it:

Create another sheet and paste the code below on the first cell:

=QUERY(table1!A:B;"SELECT A,B label B 'VALUE1'")

Then name column C1 as VALUE2 and paste the code below on cell C2:

=ARRAYFORMULA(VLOOKUP(A2:A;table2!A:B;2;FALSE))