Home

Excel: Matching email values in 2 columns

%3CLINGO-SUB%20id%3D%22lingo-sub-723989%22%20slang%3D%22en-US%22%3EExcel%3A%20Matching%20email%20values%20in%202%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-723989%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20is%20the%20formula%20for%20matching%20values%20in%20two%20columns.%20the%20email%20address%20matching%20data%20should%20be%20listed%20in%20column%20C%20and%20D%2C%20please%20see%20attached%20excel%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Target%20email%20not%20in%20actual%20email%3C%2FP%3E%3CP%3E-%20actual%20email%20not%20in%20target%20email%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20779px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121275iC1D58DEDABCE327E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.JPG%22%20title%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-723989%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-724587%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20Matching%20email%20values%20in%202%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-724587%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F61448%22%20target%3D%22_blank%22%3E%40Marvin%20Oco%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%20Marvin%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CP%3E*%20I%20used%20the%20IF%20formula%20and%20the%20SUMPRODUCT%20formula%20to%20try%20and%20solve%20the%20problem%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-724613%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20Matching%20email%20values%20in%202%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-724613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F61448%22%20target%3D%22_blank%22%3E%40Marvin%20Oco%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20C2%2C%20copied%20down%20rows%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(COUNTIF(B%3AB%2CA2)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%22No%22%2C%22Yes%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EConversely%2C%20the%20formula%20in%20D2%2C%20copied%20down%20rows%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(COUNTIF(A%3AA%2CB2)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%22No%22%2C%22Yes%22)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-724614%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20Matching%20email%20values%20in%202%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-724614%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F61448%22%20target%3D%22_blank%22%3E%40Marvin%20Oco%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CPRE%3E%3DCOUNTIFS(%24B%242%3A%24B%2448%2CA2)%3D0%3CBR%20%2F%3E%3CBR%20%2F%3E%3DCOUNTIFS(%24A%242%3A%24A%24401%2CB2)%3D0%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Marvin Oco
Super Contributor

What is the formula for matching values in two columns. the email address matching data should be listed in column C and D, please see attached excel file.

 

- Target email not in actual email

- actual email not in target email

 

Capture.JPG

 

3 Replies

@Marvin Oco 

Hey Marvin

 

Try this:

* I used the IF formula and the SUMPRODUCT formula to try and solve the problem

@Marvin Oco 

In the attached file, the formula in C2, copied down rows, is: 

=IF(COUNTIF(B:B,A2),
"No","Yes")

Conversely, the formula in D2, copied down rows, is: 

=IF(COUNTIF(A:A,B2),
"No","Yes")

@Marvin Oco 

Try this:

=COUNTIFS($B$2:$B$48,A2)=0

=COUNTIFS($A$2:$A$401,B2)=0
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies