How to get the intersection of two sets

Question

I have two lists (unique sets) in a spreadsheet and I want to get the intersection of the two.

Is there an easy way to do this?

Here's an example of what I want to achieve:

List 1  List 2  Expected result

 a       1        e
 b       2        f
 c       e      
 d       4      
 e       f      
 f       6      

Example Google Sheet

Answer 1

There is no need to jump into GAS, standard spreadsheet functions handle this easily.

Please paste the following into a cell in your example

=iferror(filter($A$2:$A, match($A$2:$A, B2:$B , false)), "no match")

The match function generates a cartesian product of the two ranges full of errors except where a match is found. It returns an index into the 2nd range when a match is found.

The filter function throws away all the crap and returns only the correctly indexed values.

The iferror helps to get a tidy result if there are no matches at all.

The ranges can be of any length, as indicated by the $A$2:$A idiom.

Update 2021/09/14:

Not long ago, I got one of those cowardly drive-by down votes with no explanation.

However, today I got this edit suggestion:

=iferror(filter($A$2:$A; match($A$2:$A; B2:$B; false)); "no match")

Has Google deprecated commas, perhaps?

Many thanks to "anonymous_user"!

@rubenrivera.mx no debe rechazar enmiendas que no entiende.

Answer 2

This little script will compare two ranges, as a formula:

function COMPARE(array1, array2) {
  var array = [];  
  for(i=0; i<array1.length; i++) {
    for(j=0; j<array2.length; j++) {
      if(array1[i][0] == array2[j][0]) {
        // the extra square brackets will make it a 2D array, 
        // aligning it vertically
        array.push([array1[i][0]]);
      }
    }
  }
  return array;
}

In your sheet you can add in cell D2 the following formula :

=COMPARE(A2:A7,B2:B7)

Add the script via the tools menu, script editor.

Answer 3

In case you are looking how to find a subtraction of two ranges (elements of range 1 that are not in range 2), like:

List 1  List 2  Expected result

 a       1        a
 b       2        b
 c       e        c
 d       4        d
 e       f      
 f       6    

Here is a formula for you:

=iferror(filter($A$2:$A, iserror(match($A$2:$A, B2:$B , false))), "no match")

Answer 4

This little script will compare two ranges, as a formula:

function intersect(array1, array2) {
  var array = [];  
  for(i=0; i<array1.length; i++) {
    for(j=0; j<array2.length; j++) {
      if(array1[i][0] == array2[j][0]) {
        // the extra square brackets will make it a 2D array, 
        // aligning it vertically
        array.push([array1[i][0]]);
      }
    }
  }
  return array;
}

Extra:

function extersect(array1, array2) {
  var array = [];  
  var tmp = true;
  for(i=0; i<array1.length; i++) {
    for(j=0; j<array2.length; j++) {
      if(array1[i][0] == array2[j][0]) {
        // the extra square brackets will make it a 2D array, 
        // aligning it vertically
        tmp = false;
      }      
    }
    if(tmp == true) 
    {
      array.push([array1[i][0]]);
    }
    tmp = true;
  }
  return array;
}

In your sheet you can add in cell D2 the following formula :

=intersect(A2:A7,B2:B7)

Add the script via the tools menu, script editor.

=intersect(A2:A7,B2:B7) returns results that present on both arrays array1, array2.

=extersect(A2:A7,B2:B7) returns values of array1 that does not exists in range B2:B7