Question about using the IF function to display matching text/pattern matching

[mikelopez]

Hello everyone.

I just a have a general question about using the IF function to display matching names.

My spreadsheet looks like this

edcoronado	edcoronado123
joel	joelsmith
don schmit	donschimt4523
dansmith	dan jones
The above is in the A and B cells respectively. What I want to know is what would be the proper syntax formula I would use if I wanted to match and display the names that are similar to each other? What I am trying to create and accomplish is a formula using the IF function where if the name in A1 is similar or the exact name in B1,( as well as A2 and B2 respectively) I want the function to grab the text that is similar and display it in column D. I think I can use the IF function but I am not sure. Any help would be greatly appreciated. THE FORMULA I AM TRYING TO USE IS =IF(A1=B1, " ", "9999" Where if the letters are similar I want the text that is similar to be displayed and if it is not, I want 9999 to appear. Again any help would be greatly appreciated.

<tbody>
</tbody>

 

[shyy]

Suggest you look up FUZZYLOOKUP on this board. It is a great UDF.

You can away with some of the things by using SEARCH and wildcard

 

[mikelopez]

I couldn't find the FUZZYLOOKUP. When I did find it it looked like beyond the scope of what I am trying to do. I just want a formula that will help me display the similar text to each other. Are you sure there isn't a simpler way? I should also let you know that I am using Microsoft Excel 2003.

 

[shyy]

For your following example:

[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]edcoronado
[/TD]
[TD]edcoronado123 - possible with a search and wildcard
[/TD]
[/TR]
[TR]
[TD]joel
[/TD]
[TD]joelsmith - possible with a search and wildcard
[/TD]
[/TR]
[TR]
[TD]don schmit
[/TD]
[TD]donschimt4523 - fuzzyvlookup unless you search the first word that contains two or more words using the space "don"
[/TD]
[/TR]
[TR]
[TD]dansmith
[/TD]
[/TR]
</TBODY>[/TABLE]



situations like don schmit and dansmith can never be 100%, FUZZYVLOOKUP gives you 3 types of degrees. You might not even get the result you want, but for me it has worked and saved me about 80% if manual editing.


Excel 2010

	A	B	C
edcoronado	edcoronado123
edcoronado	edcoronado 123
edcoronado	edcoronad123
edcoronado	edcoronado5345123

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]C1[/TH]
[TD="align: left"]=IFERROR(SEARCH("*"&A1&"*",B1),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]C2[/TH]
[TD="align: left"]=IFERROR(SEARCH("*"&A2&"*",B2),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]C3[/TH]
[TD="align: left"]=IFERROR(SEARCH("*"&A3&"*",B3),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]C4[/TH]
[TD="align: left"]=IFERROR(SEARCH("*"&A4&"*",B4),"")[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[mikelopez]

Hello again.

I tried doing what you told me to do above in the C1 cell. However when I did it I received this in the D1 cell where I want the data to appear. I received the #NAME? error. Are you sure you gave me the correct input to put in the cell where you want your information to appear?

 

[shyy]

You are right, just read that you are using excel 2003. IFERROR Is 2007 +

Try this instead, I also added another formula solution to COLUMN D

Excel 2010
	A	B	C	D
1	edcoronado	edcoronado123	1	1
2	edcoronado	edcoronado 123	1	1
3	edcoronado	edcoronad123
4	edcoronado	edcoronado5345123	1	1
Sheet1

Cell Formulas
Range	Formula
C1	=IF(ISERROR(SEARCH("*"&A1&"*",B1)),"",SEARCH("*"&A1&"*",B1))
C2	=IF(ISERROR(SEARCH("*"&A2&"*",B2)),"",SEARCH("*"&A2&"*",B2))
C3	=IF(ISERROR(SEARCH("*"&A3&"*",B3)),"",SEARCH("*"&A3&"*",B3))
C4	=IF(ISERROR(SEARCH("*"&A4&"*",B4)),"",SEARCH("*"&A4&"*",B4))
D1	=IF(COUNTIF(B1,"*"&A1&"*")=0,"",COUNTIF(B1,"*"&A1&"*"))
D2	=IF(COUNTIF(B2,"*"&A2&"*")=0,"",COUNTIF(B2,"*"&A2&"*"))
D3	=IF(COUNTIF(B3,"*"&A3&"*")=0,"",COUNTIF(B3,"*"&A3&"*"))
D4	=IF(COUNTIF(B4,"*"&A4&"*")=0,"",COUNTIF(B4,"*"&A4&"*"))

Hello again.

I tried doing what you told me to do above in the C1 cell. However when I did it I received this in the D1 cell where I want the data to appear. I received the #NAME? error. Are you sure you gave me the correct input to put in the cell where you want your information to appear?

 

[mikelopez]

I used your formula and it was helpful however the number 1 is not something I wanted to appear in Cell D. What I want is if the text in column A is similar to the text in column B I want the formula in column D to grab the letters that are similar to one another and display them in column D.

For example

column A has: edcoronado
column B has: edcoronado123

I want column D to display: edcoronado

Likewise

don schmit in A
donschmit4523 in B
column D will display donschmit.

Are you sure that these are the only formulas that will work because they are only returning numbers?

 

[shyy]

That was just to prove the logic if it works or not. JUst reference the original value that you used to do the SEARCH.

For the Don Smitch example I can't help you on that.

Excel 2010
	A	B	C	D
1	edcoronado	edcoronado123	edcoronado	edcoronado
2	edcoronado	edcoronado 123	edcoronado	edcoronado
3	edcoronado	edcoronad123
4	edcoronado	edcoronado5345123	edcoronado	edcoronado
Sheet1

Cell Formulas
Range	Formula
C1	=IF(IF(ISERROR(SEARCH("*"&A1&"*",B1)),"",SEARCH("*"&A1&"*",B1))=1,A1,"")
C2	=IF(IF(ISERROR(SEARCH("*"&A2&"*",B2)),"",SEARCH("*"&A2&"*",B2))=1,A2,"")
C3	=IF(IF(ISERROR(SEARCH("*"&A3&"*",B3)),"",SEARCH("*"&A3&"*",B3))=1,A3,"")
C4	=IF(IF(ISERROR(SEARCH("*"&A4&"*",B4)),"",SEARCH("*"&A4&"*",B4))=1,A4,"")
D1	=IF(COUNTIF(B1,"*"&A1&"*")=0,"",A1)
D2	=IF(COUNTIF(B2,"*"&A2&"*")=0,"",A2)
D3	=IF(COUNTIF(B3,"*"&A3&"*")=0,"",A3)
D4	=IF(COUNTIF(B4,"*"&A4&"*")=0,"",A4)

 

[mikelopez]

I tried the formula in cell C1 and it works.
Thank you so much for your help. I truly appreciate it. My last question is if I have the following:

johnsmith1882 in cell A1
and
johnsmith in cell B2 for example, could I just reverse the order of what is in cell C1 where SEARCH("*"&A1&"*",B1))=1,A1,"") is at the beginning?

I tried to use that formula for Cell C1 and I did change the cell numbers but nothing came up when I did it.
Again thank you so much.

 

[mikelopez]

I tried the formula in cell C1 and it works.
Thank you so much for your help. I truly appreciate it. My last question is if I have the following:

johnsmith1882 in cell A1
and
johnsmith in cell B1 for example, could I just reverse the order of what is in cell C1 where SEARCH("*"&A1&"*",B1))=1,A1,"") is at the beginning?

I tried to use that formula for Cell C1 and I did change the cell numbers but nothing came up when I did it.
Again thank you so much.