27

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

1
  • Can you share a doc with us and explain the expected result a bit? Feb 18, 2013 at 19:14

5 Answers 5

34

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.

2
  • 2
    It is to be noted that it is not case sensitive, while the answer from @Daniel, based on query, is case sentitive.
    – thdox
    Sep 27, 2022 at 6:22
  • @thdox Thanx for the clarification. I'm amazed that my answer is still relevant after 8 years. Dec 1, 2022 at 13:42
4

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")
3

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.

3
  • I welcome plain old formula's. I could think of them immediately..... Feb 19, 2013 at 12:36
  • 1
    could should/must be couldn't....... Feb 19, 2013 at 12:48
  • 1
    Thanks, @Jacob! I never had a real reason to use formulas in gDocs - thanks for opening me up to this world!
    – NoamNelke
    Feb 19, 2013 at 19:03
2

Here's a modern answer using Google Sheet query function. This is an array function, meaning that it will dynamically generate rows and columns. This precludes the need to add a function per line, you can instead point to the two ranges and get a dynamically sized result.

This is especially helpful if you want to feed this further into another function which needs to operate on the intersection.

If your 2 lists were in column A and B, then:

Case-sensitive soltution:

=query(A2:A, "
  select A
  where A matches '"&textjoin("|", true, B2:B)&"'
")

Case-insensitive solution:

As @thdox noted in the comments, the above answer is case-sensitive. To make a case-insensitive variant, we can use the Google Query Language lower scalar function as well as the Google Sheet lower function:

=query(A2:A, "
  select A
  where lower(A) matches '"&lower(textjoin("|", true, B2:B))&"'
")

Explanation:

What's happening is that the first column is used as a source, then we filter this source with the Google Query Language where clause's matches operator to RegEx match on any of the B column values (turned into a single string with the RegEx OR "|" character via the textjoin function).

Note:

This method may break if the B list has entries with special regex characters like \.*[](){}+^$|. In this case you would need to escape these special characters with a backslash \ for this method to work.

3
  • 2
    It is to be noted that it is case sensitive, while the answer from @Martin Bramwell, based on filter, is not case sentitive.
    – thdox
    Sep 27, 2022 at 6:22
  • 1
    Thanks @thdox. Based on your comment I updated my answer and added a case-insensitive version.
    – Daniel
    Sep 29, 2022 at 17:26
  • 1
    It's good to see these improvements. Very cool! Thanx. Dec 1, 2022 at 13:43
0

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.