Filtering 2 columns for matching values-CodePudding

I have 2 columns in excel. I want to find matching values within these two columns and put them in a third column.

Here’s what I tried:

=FILTER(A:A,COUNTIF(A:A,B:B))

returns 0

Sample data:

A B

AA01 EE04

CC02 CD06

BB03 AA02

AA04 AA01

EE04 CC02

Expected output:

AA01

CC02

EE04

CodePudding user response：

You can use MATCH to find if value in A cell is in B column.

If you have Excel 365 you can use formula in C1:

=FILTER(BYROW(A1:A5,LAMBDA(row,IF(ISERROR(MATCH(row,$B$1:$B$5,0)),"",row))),BYROW(A1:A5,LAMBDA(row,IF(ISERROR(MATCH(row,$B$1:$B$5,0)),"",row)))<>"","")

Result:

Otherwise use =IF(ISERROR(MATCH(A1,$B$1:$B$5,0)),"",A1) in C1 dragged down, FILTER result in D1 with =FILTER(C1:C5,C1:C5<>""):

CodePudding user response：

How about using XMATCH() or COUNTIF()

• Formula used in cell D1

=FILTER(A1:A5,ISNUMBER(XMATCH(A1:A5,B1:B5)))

• Formula used in cell E1

=FILTER(A1:A5,COUNTIF(B1:B5,A1:A5))