Google Sheets – Get Unique Combinations of Values from Two Tables

google sheets

I've got 2 tables and on a third table I want to have all unique combinations of table2.a, table1.b where table1.c==table2.c

SELECT table2.a, table1.b FROM table1 JOIN table2 WHERE table1.c=table2.c

In sql I would be using inner join or join (I think) but I can't seem to find a way to do this using google sheets query()

It doesn't need to join the actual keys together like I have in table3.a I have already added an arrayformula to handle that. The prefered output would be a 2d array that I can fill in table3.b:c

table1

a b c
  4 7
  5 8
  6 9

table2

a b c
1   7
2   8
3   9

table3 (wanted output)

a  b  c
14 1  4 
25 2  5
36 3  6

I've tried to replicate what I would use in sql to get the results I want, but to no avail as the query() method doesn't work the same.

Things I have tried.
=query("SELECT" table1!A, table2!A "FROM "table1, table2" WHERE" table1!C = table2!C) doesnt work and isn't the correct syntax

=query({table1!A2:A; table2!A2:A})
this gets me the list of both in one column,

=query({table1!A2:C; table2!A2:C}, "select Col1 where Col2='3'")
this gets me a list of all but only checks a single column (first column from both sheets) I think this is getting closer if i can find a way to set it to check a certain column from each sheet rather than the default Col1, Col2 etc.

Best Answer

I would write a custom function for this, to be used as 

=combinations(table1!B2:B, table1!C2:C, table2!A2:A, table2!C2:C)

that is, the parameters are ValueColumn1, KeyColumn1, ValueColumn2, KeyColumn2.

The logic is entirely straightforward: run a double loop and collect whatever matches. 

function combinations(range1, key1, range2, key2) {
  var output = [];
  if (range1.length != key1.length || range2.length != key2.length) {
    throw new Error('Each range must have the same length as its key'); 
  }
  for (var i = 0; i < range1.length; i++) {
    for (var j = 0; j < range2.length; j++) {
      if (key1[i][0] === key2[j][0] && key1[i][0] !== '') {
        output.push([range1[i][0], range2[j][0]]);
      }
    }
  }
  return output;
}
Related Topic