SOLVED

Cell contains partial text match

Copper 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 (Copper 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

1 best response

Accepted Solutions
best response confirmed by KMorrison5151 (Copper Contributor)
Solution

Hello @KMorrison5151,

 

Starting in cell C2:

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

View solution in original post