Compare 2 Lists Return Matches

Question

Just trying compare two lists in columns A B and look for text and integers that are matches in column C. Not sure what function makes sense xlookup or index/match? I only have 2019 excel capabilities.

List 1	List 2	Results
Apple	Pear	Apple
Pear	Apple	1
Kiwi	3	2
1	2	3
2	1	.123Banana
3	.123Banana	Pear
Strawberry	XYZ
Melon	.123Kiwi
.123Banana
ABC
.123KiwU

Answer 1

Common Values in Two Columns

MS365, Excel 2021 (Spills)

=FILTER(A2:A12,ISNUMBER(XMATCH(A2:A12,B2:B9)))

Older Versions (Copy Down)

=IFERROR(INDEX($A$2:$A$12,SMALL(IF(ISNA(MATCH($A$2:$A$12,$B$2:$B$9,0)),"",ROW($A$2:$A$12)-ROW($A$2)+1),ROW(A2)-ROW($A$2)+1)),"")

Answer 2

You could use this which is based on a traditional method of finding unique values in a list in earlier versions of Excel:

=XLOOKUP(1,(COUNTIF(B$2:B$9,A$2:A$12)>0)*(COUNTIF(D$1:D1,A$2:A$12)=0),A$2:A$12,"")

in (say) D2 and pulled down.

List 1	List 2	Results	Test
Apple	Pear	Apple	Apple
Pear	Apple	1	Pear
Kiwi	3	2	1
1	2	3	2
2	1	.123Banana	3
3	.123Banana	Pear	.123Banana
Strawberry	XYZ
Melon	.123Kiwi
.123Banana
ABC
.123KiwU