INNER JOIN in Google Spreadsheets

Question

I don't know or remember the technical name of what I'm looking for but I think an example will be enough for you to understand exactly what I'm looking for.

Given table A

a   x1
b   x2
c   x1

and Table B

x1  x
x1  y
x1  z
x2  p
x2  z

I Need Table C

a   x
a   y
a   z
b   p
b   z
c   x
c   y
c   z

I'm looking for a formula or a set of them to get table C

I guess just need to add an extra row on the C table with each value of the first column on the table A for each corresponding value of TableA!Column2 to TableB!Column1 But I can't find how

I think this is a simple SQL Inner Join.

Answer 1

What you are trying to do is a JOIN in SQL, which duplicates values. Inner, left, right or full defines the behavior when there are no matches on both sides.

The easiest way I can think to do this is by using Google Apps Script to make a custom function to use in a formula:

function JOINRANGES(range1, index1, range2, index2) {
 const result = []
 for(let row1 of range1) {
   for (let row2 of range2) {
     if (row1[index1] == row2[index2]) {
       const r = [...row1, ...row2]
       // Remove the keys themselves
       r.splice(row1.length+index2, 1)
       r.splice(index1, 1)
       result.push(r)
     }
   }
 }
 return result
}

Then you may use it in a sheets formula on the top-left column:

=ARRAYFORMULA(JOINRANGES(A1:B3, 1, D1:E6, 0))

The first and the third arguments are the ranges, and the second and the fourth arguments are the index (starting with 0) of the column inside the range.

The final result:

If you don’t know about Google Apps Script, the about page in the google-apps-script tag has a lot of guides and documentation that is very useful.

Answer 2

try:

=ARRAYFORMULA({TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$A2:A&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D}, 
 "select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦"))), IFNA(VLOOKUP(
 TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$B2:B&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D}, 
 "select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦")))&COUNTIFS(
 TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$A2:A&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D}, 
 "select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦"))), 
 TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$A2:A&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D}, 
 "select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦"))), ROW(INDIRECT("O1:O"&COUNTA(
 TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$B2:B&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D}, 
 "select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦")))))), "<="&ROW(INDIRECT("O1:O"&COUNTA(
 TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$B2:B&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D}, 
 "select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦"))))))), 
 {D2:D&COUNTIFS(D2:D, D2:D, ROW(O2:O), "<="&ROW(O2:O)), E2:E}, 2, 0))})