How to use COUNTIF to check if a value is in another list
You have a list of names, and a second list of ones to watch for. This checks each name against that second list and tells you whether it is there.
The formula
=IF(COUNTIF(D:D,A2)>0,"Yes","No") in column B
What each part does
COUNTIF(D:D,A2)
How many times this row's value shows up in the other column.
>0
One or more means it is there.
How it works, and what to watch for
COUNTIF counts how many times the value shows up in the other list. Anything above zero means it is present, so the test is simply whether the count is more than zero.
If a name you know is on the list comes back as not found, check for spaces on either side. TRIM around both parts is the usual cure.
This is the friendly way to compare two lists before you merge or clean them.
Questions
How do I show only the ones that are missing?
Flip the test: =IF(COUNTIF(list,value)=0,"Missing",""). That leaves a mark only against the ones that are not on the other list.
Does this care about capital letters?
No, COUNTIF treats "Acme" and "acme" as the same. If case matters to you, EXACT inside an array is the way to go.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
