# How to get the intersection of two sets

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
```

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

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

``````=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")
``````

Many thanks to "anonymous_user"!

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

• It is to be noted that it is not case sensitive, while the answer from @Daniel, based on query, is case sentitive. 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

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

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] == array2[j]) {
// the extra square brackets will make it a 2D array,
// aligning it vertically
array.push([array1[i]]);
}
}
}
return array;
}
``````

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

``````=COMPARE(A2:A7,B2:B7)
``````

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

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.

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

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] == array2[j]) {
// the extra square brackets will make it a 2D array,
// aligning it vertically
array.push([array1[i]]);
}
}
}
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] == array2[j]) {
// the extra square brackets will make it a 2D array,
// aligning it vertically
tmp = false;
}
}
if(tmp == true)
{
array.push([array1[i]]);
}
tmp = true;
}
return array;
}
``````

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

``````=intersect(A2:A7,B2:B7)
``````