I have two columns of email addresses (A & B) and would like to determine which emails appear in both list. Everything I googled either highlighted matching cells, requires third party tools like Kutools or only matches if the same value is on the same row.
| A | B | C |
|---|---|---|
| 1 | 3 | 3 |
| 2 | 4 | 4 |
| 3 | 5 | 5 |
| 4 | 6 | |
| 5 | 7 | |
| 8 | ||
| 9 |
I assumed this was a common thing to do in Excel, but can't seem to find a simple solution.
CodePudding user response：
With Office 365 we can use FILTER:
=FILTER(A:A,ISNUMBER(MATCH(A:A,B:B,0)))
Without Office 365 put this in C1 and copy down till empty:
=IFERROR(INDEX(A:A,AGGREGATE(15,7,ROW(A1:A5)/(ISNUMBER(MATCH(A1:A5,B:B,0))),ROW($ZZ1))),"")
