Checking two lists of emails for duplicates, returning "yes", "no", or "&qu-CodePudding

I'm trying to design a sheet to be used within a team, so there would be fields that have new data entered regularly.

Goal: check for the cell contacts (E2) against a range of cells for exact matches (A2:A), in F2 (where formula would be entered) enter either Yes, No, or blank as appropriate. If there is a duplicate in the A range, then Yes would be displayed in the F column.

requirements:

must return only exactly duplicate matches
must display a blank in F2 if E2 is blank
should work dynamically when new content is entered into the E column

Tried already: False success with:

=IF(ISERROR(VLOOKUP($E2,$A$2:$A,0)),"YES","NO")

but it doesn't reliably detect if there is a duplicate. It returns a Yes when it should be a No. This solution doesn't display a blank if the cell in E is blank either and I couldn't work out how to add the 'if' onto this formula

I'm wondering if VBA would be a better approach but that's a new area for me, so hoping there's a formula that would help with this.

CodePudding user response：

COUNTIF may be an approach (also think there may be typos in your example). In F2:

=IF($E2="","",IF(COUNTIF($A:$A,$E2)>1,"YES","NO"))