How to Find Items Missing from Another List in Excel

Compare two lists with COUNTIF: flag who hasn’t replied, count the missing names, and catch entries that shouldn’t be there.

You will learn to

• Check whether one item appears in another list
• Count every item missing from the second list
• Compare in the other direction while ignoring blanks

How it works

To see whether a value from list A exists in list B, count it there: =COUNTIF($C$2:$C$11,A3). Zero means it is missing. Wrap it in IF for a readable flag such as "No reply". This works for guest lists and RSVPs, SKU lists and stock counts, invoice numbers and payments.

To count every missing item at once, feed the whole first list into COUNTIF and test for zeros inside SUMPRODUCT. Always check the other direction too — entries in list B that were never in list A are often typos or unexpected extras. In Microsoft 365, FILTER(A2:A11,COUNTIF(C2:C11,A2:A11)=0) lists the missing names.

=IF(COUNTIF($C$2:$C$11,A3)=0,"No reply","Replied")