Home

FORMULA TO MATCH TEXT STRING IN 2 COLUMNS

%3CLINGO-SUB%20id%3D%22lingo-sub-869217%22%20slang%3D%22en-US%22%3EFORMULA%20TO%20MATCH%20TEXT%20STRING%20IN%202%20COLUMNS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869217%22%20slang%3D%22en-US%22%3E%3CP%3Ewhat%20is%20the%26nbsp%3BFORMULA%20TO%20MATCH%20TEXT%20STRING%20IN%202%20COLUMNS%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecell%20text%20data%20string%20in%20column%20A%20if%20matches%20text%20in%20column%20B%20or%20C%20should%20result%20%3CSTRONG%3E%22match%22%3C%2FSTRONG%3E%20in%20column%20D%2C%20%3CSTRONG%3E%22no%20match%22%3C%2FSTRONG%3E%20if%20otherwise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20see%20attached%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%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133004i05EB8C62B79ABA45%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%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-869217%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-869354%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULA%20TO%20MATCH%20TEXT%20STRING%20IN%202%20COLUMNS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869354%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%3EPlease%20try%20this...%3C%2FP%3E%3CP%3EIn%20D2%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(COUNTIF(B2%3AC2%2C%22*%22%26amp%3BA2%26amp%3B%22*%22)%2C%22MATCH%22%2C%22NO%20MATCH%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20copy%20it%20down.%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871728%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULA%20TO%20MATCH%20TEXT%20STRING%20IN%202%20COLUMNS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871728%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%20formula%20should%20look%20for%20all%20cell%20data%20in%20b2%20to%20b3000%20and%20c2%20to%20c3000%2C%20how%20can%20we%20include%20them%3F%20thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871771%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULA%20TO%20MATCH%20TEXT%20STRING%20IN%202%20COLUMNS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871771%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%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20place%20the%20formula%20I%20proposed%20in%20D2%20and%20copy%20it%20down%2C%20it%20will%20return%20the%20same%20desired%20output%20you%20mocked%20up%20manually.%20But%20it%20seems%20I%20am%20missing%20your%20logic%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20you%20please%20explain%20the%20logic%20behind%20your%20desired%20output%20you%20showed%20in%20column%20D%3F%3C%2FP%3E%3CP%3Ee.g.%20in%20Column%20D%2C%20you%20have%20MATCH%20in%20D2%20and%20NO%20MATCH%20in%20D3%2C%20can%20you%20explain%20the%20logic%20behind%20these%20two%20desired%20output%3F%20Maybe%20then%20I%20would%20pick%20the%20logic%20and%20derive%20a%20suitable%20formula%20to%20return%20the%20desired%20output.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20explaining%20the%20logic%2C%20take%20the%20value%20of%20A2%20and%20A3%20and%20let%20me%20know%20all%20the%20manual%20steps%20you%20would%20take%20to%20get%20the%20desired%20output%20as%20shown%20in%20D2%20and%20D3%20in%20your%20sample%20file%20considering%20just%20four%20rows%20of%20data.%3C%2FP%3E%3CP%3EAll%20I%20want%20to%20know%20why%20D2%20has%20MATCH%20in%20it%20and%20why%20D3%20has%20NO%20MATCH%20in%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871997%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULA%20TO%20MATCH%20TEXT%20STRING%20IN%202%20COLUMNS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871997%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA2%20matches%20text%20string%20in%20c5%2C%20match%3B%20A3%20do%20not%20match%20in%20any%20cell%20in%20column%20B%20and%20C%2C%20no%20match%2C%20A4%20matches%20in%20B3%2C%20match%3B%20A5%20do%20not%20match%20in%20any%20cell%20in%20B%20and%20C%2C%20no%20match%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-872030%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULA%20TO%20MATCH%20TEXT%20STRING%20IN%202%20COLUMNS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-872030%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%3EThanks%20for%20explaining%20the%20logic.%3C%2FP%3E%3CP%3EPlease%20try%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20D2%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(COUNTIF(%24B%242%3A%24C%243000%2C%22*%22%26amp%3BA2%26amp%3B%22*%22)%2C%22MATCH%22%2C%22NO%20MATCH%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20copy%20it%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20placed%20the%20formula%20in%20Green%20Cells%20in%20column%20E%20in%20the%20attached.%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20you%20are%20getting%20the%20desired%20output%20now.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Marvin Oco
Super Contributor

what is the FORMULA TO MATCH TEXT STRING IN 2 COLUMNS?

 

cell text data string in column A if matches text in column B or C should result "match" in column D, "no match" if otherwise.

 

please see attached

 

Capture.JPG

 

 

5 Replies

@Marvin Oco 

Please try this...

In D2

=IF(COUNTIF(B2:C2,"*"&A2&"*"),"MATCH","NO MATCH")

and copy it down.

@Subodh_Tiwari_sktneer  formula should look for all cell data in b2 to b3000 and c2 to c3000, how can we include them? thanks

@Marvin Oco 

 

If you place the formula I proposed in D2 and copy it down, it will return the same desired output you mocked up manually. But it seems I am missing your logic here.

 

Would you please explain the logic behind your desired output you showed in column D?

e.g. in Column D, you have MATCH in D2 and NO MATCH in D3, can you explain the logic behind these two desired output? Maybe then I would pick the logic and derive a suitable formula to return the desired output.

 

While explaining the logic, take the value of A2 and A3 and let me know all the manual steps you would take to get the desired output as shown in D2 and D3 in your sample file considering just four rows of data.

All I want to know why D2 has MATCH in it and why D3 has NO MATCH in it?

 

@Subodh_Tiwari_sktneer 

 

A2 matches text string in c5, match; A3 do not match in any cell in column B and C, no match, A4 matches in B3, match; A5 do not match in any cell in B and C, no match

 

Please see attached

@Marvin Oco 

Thanks for explaining the logic.

Please try this...

 

In D2

=IF(COUNTIF($B$2:$C$3000,"*"&A2&"*"),"MATCH","NO MATCH")

and copy it down.

 

I have placed the formula in Green Cells in column E in the attached.

Please let me know if you are getting the desired output now.

 

 

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies