Compare Two Lists: Count Shared Matches & Return First Overlap

Reconcile two rosters cleanly—even when List 2 contains duplicate rows!

A membership flag counts each List1 row at most once, even if List2 repeats the identifier. It does not deduplicate List1: repeated matching List1 rows still contribute repeatedly. Column C therefore keeps only the first occurrence in List1 for a distinct overlap count.

Both lists are text identifiers. Matching ignores case, preserves spaces and treats wildcard characters literally by escaping ~, * and ? and using an equality criterion. Blank List1 cells and formulas returning empty strings contribute zero; nonblank nontext List1 entries are rejected.

The first overlap follows List1 source order. The two counts answer different questions: how many rows matched, and how many distinct identifiers matched under case-insensitive comparison. Neither validates that the identifiers belong to a real account or inventory scheme.

Count matching List1 rows

Sum the B flags; repeated List1 identifiers remain separate rows.

=IF(COUNTIF(B2:B6,-1)>0,"Invalid List1",SUM(B2:B6))

Return First Overlapping Value Between Two Lists

Finds the first row where membership flag B2:B6 equals 1.

=IF(COUNTIF(B2:B6,-1)>0,"Invalid List1",XLOOKUP(1,B2:B6,A2:A6,"No Overlap"))

Create membership flags

Enter in B2 and fill through B6. The practice workbook includes these formulas.

=IF(COUNTBLANK(A2)>0,0,IF(NOT(ISTEXT(A2)),-1,--(COUNTIF($D$2:$D$5,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?"))>0)))

Keep only the first matching occurrence

Enter in C2 and fill through C6. The growing range retains only the first occurrence in List1.

=IF(B2<>1,0,--(COUNTIF($A$2:A2,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?"))=1))

Count distinct shared identifiers

Sum the C flags after checking List1 input validity.

=IF(COUNTIF(B2:B6,-1)>0,"Invalid List1",SUM(C2:C6))

Further reading

• Microsoft Support — COUNTIF function