Home

Compare two columns of data

%3CLINGO-SUB%20id%3D%22lingo-sub-901086%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20two%20columns%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-901086%22%20slang%3D%22en-US%22%3E%3CBR%20%2F%3E1%202%20YES%3CBR%20%2F%3E2%208%20NO%3CBR%20%2F%3E3%209%20NO%3CBR%20%2F%3E4%203%20YES%3CBR%20%2F%3E5%207%20NO%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-901077%22%20slang%3D%22en-US%22%3ECompare%20two%20columns%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-901077%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20for%20a%20formula%20that%20produces%20the%20following%20please%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EIf%20range%20of%20values%20in%20column%20B%20also%20appear%20within%20range%20of%20values%20in%20column%20A%2C%20column%20C%20%3D%20YES%20or%20NO%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20this%20is%20probably%20a%20very%20basic%20query%20and%20that%20the%20answer%20is%20out%20there%20i%20just%20cant%20work%20out%20how%20to%20word%20my%20question!%20Hopefully%20this%20makes%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eeg%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EYES%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3ENO%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3ENO%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3EYES%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3ENO%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-901077%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-901169%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20two%20columns%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-901169%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422587%22%20target%3D%22_blank%22%3E%40nicole_l%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20below%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(IF(MATCH(B2%2C%24A%242%3A%24A%246%2C0)%26gt%3B0%2C%22Yes%22%2C%22No%22)%2C%22No%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20file%20is%20also%20attached%20for%20your%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-901250%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20two%20columns%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-901250%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422587%22%20target%3D%22_blank%22%3E%40nicole_l%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20assuming%20your%20table%20starts%20in%20cell%20A1%2C%20write%20this%20formula%20in%20Cell%20C2%20and%20copy%20down%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(COUNTIF(A%3AA%3BB2)%3D1%3B%22YES%22%3B%22NO%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
nicole_l
Occasional Visitor

I am looking for a formula that produces the following please

If range of values in column B also appear within range of values in column A, column C = YES or NO

 

I know this is probably a very basic query and that the answer is out there i just cant work out how to word my question! Hopefully this makes sense.

 

eg;

ABC
12YES
28NO
39NO
43YES
57NO
2 Replies

Hi @nicole_l 

 

Please try below formula:

 

=IFERROR(IF(MATCH(B2,$A$2:$A$6,0)>0,"Yes","No"),"No")

 

Sample file is also attached for your reference.

 

Thanks

Tauqeer

 

@nicole_l 

 

Hi, assuming your table starts in cell A1, write this formula in Cell C2 and copy down:

 

=IF(COUNTIF(A:A;B2)=1;"YES";"NO")

 

 
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies