SOLVED

Cell contains partial text match

%3CLINGO-SUB%20id%3D%22lingo-sub-1232500%22%20slang%3D%22en-US%22%3ECell%20contains%20partial%20text%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1232500%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20columns%20of%20data.%20I%20would%20like%20a%20fuzzy%20match%20from%20B2%20to%20A2.%20So%20if%20value%20from%20b2%20is%20anywhere%20in%20a2%20that%20is%20a%20match.%20In%20attached%20spreadsheet%20the%20result%20would%20be%20No%2C%20No%2C%20Yes%2C%20No%2C%20Yes%20etc.%20Can%20anyone%20suggest%20the%20function%20for%20that%20request%3F%20Thank%20you!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1232500%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1232528%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20contains%20partial%20text%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1232528%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584261%22%20target%3D%22_blank%22%3E%40KMorrison5151%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStarting%20in%20cell%20C2%3A%3C%2FP%3E%3CPRE%3E%3DIFERROR(IF(SEARCH(B2%2CA2)%26gt%3B0%2C%22YES%22)%2C%22NO%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1232548%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20contains%20partial%20text%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1232548%22%20slang%3D%22en-US%22%3E%3CP%3EMany%20thank%20yous!%20Works%20perfectly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1232571%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20contains%20partial%20text%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1232571%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584261%22%20target%3D%22_blank%22%3E%40KMorrison5151%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1232716%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20contains%20partial%20text%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1232716%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMay%20I%20ask%20you%20one%20more...I%20have%20codes%20in%20column%20a.%20I%20want%20to%20test%20those%20codes%20against%20a%20table%20and%20return%20a%20results%20based%20on%20that%20table.%20Any%20thoughts%20on%20the%20function%20to%20use%3F%20In%20my%20file%20below%2C%20in%20column%20b%20-%20if%20a2%20contains%20%22PS01%22%20then%20%22Valid%22%20else%20if%20a2%20contains%20%22PE11%22%20then%20%22Disconnected%22%20else%20N%2FA.%20Thank%20you!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have two columns of data. I would like a fuzzy match from B2 to A2. So if value from b2 is anywhere in a2 that is a match. In attached spreadsheet the result would be No, No, Yes, No, Yes etc. Can anyone suggest the function for that request? Thank you! 

9 Replies
best response confirmed by KMorrison5151 (Occasional Contributor)
Solution

Hello @KMorrison5151,

 

Starting in cell C2:

=IFERROR(IF(SEARCH(B2,A2)>0,"YES"),"NO")

Many thank yous! Works perfectly.

@KMorrison5151 

 

You're welcome!

@PReagan 

 

May I ask you one more...I have codes in column a. I want to test those codes against a table and return a results based on that table. Any thoughts on the function to use? In my file below, in column b - if a2 contains "PS01" then "Valid" else if a2 contains "PE11" then "Disconnected" else N/A. Thank you!!

 

Hello @KMorrison5151,

 

For Valid/Disconnected, that could be:

=IFERROR(IF(SEARCH($E$2,$A3),$F$2),"")&
IFERROR(IF(SEARCH($E$3,$A3),$F$3),"")

For Land/Cell/VOIP, that could be:

=IFERROR(IF(SEARCH($E$6,$A2),$F$6),"")&
IFERROR(IF(SEARCH($E$7,$A2),$F$7),"")&
IFERROR(IF(SEARCH($E$8,$A2),$F$8),"")

 

PReagan_0-1584463882097.png

 

Again, thank you! You make it look so easy. I am humbled.

Glad to help!

@PReagan, would it be possible to do the "if then data" on another worksheet?

@Chant711 

 

Absolutely! Simply adjust your cell references to the cells on the other sheet. For example, =Sheet2!A1