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
There is no need to jump into GAS, standard spreadsheet functions handle this easily.
Please paste the following into a cell in your example
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.
This little script will compare two ranges, as a formula:
In your sheet you can add in cell D2 the following formula :
Add the script via the tools menu, script editor.