Forum Discussion

Copper Contributor

Mar 17, 2020

Solved

Cell contains partial text match

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 ...

excel

PReagan
Mar 17, 2020
Hello KMorrison5151,

Starting in cell C2:
=IFERROR(IF(SEARCH(B2,A2)>0,"YES"),"NO")

KMorrison5151

Copper Contributor

Mar 17, 2020

Many thank yous! Works perfectly.

PReagan

Bronze Contributor

Mar 17, 2020

KMorrison5151

You're welcome!

KMorrison5151
Copper Contributor
Mar 17, 2020
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!!
- PReagan
  Bronze Contributor
  Mar 17, 2020
  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),"")
  - KMorrison5151
    Copper Contributor
    Mar 17, 2020
    Again, thank you! You make it look so easy. I am humbled.