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]

Yep just switch the cells and it should be fine.

=IF(IF(ISERROR(SEARCH("*"&A1&"*",B1)),"",SEARCH("*"&A1&"*",B1))=1,A1,"") now becomes

=IF(IF(ISERROR(SEARCH("*"&B1&"*",A1)),"",SEARCH("*"&B1&"*",A1))=1,B1,"")

"*" - acts as a wildcard so your saying anything before or after as long as the cell contains the text johnsmith.

 

[mikelopez]

I switched the cells and it didn't work. I didn't get johnsmith.

here is what I have:
johnsmith1882 in cell A7
johnsmith in cell B7.

I tried the command function you told me to do:

=IF(IF(ISERROR(SEARCH("*"&B7&"*",A7)),"",SEARCH("*"&B7&"*",A7))=1,B7,"")

and it didn't work.

Do you have any other suggestions and can this command work with other cell numbers?

 

[mikelopez]

Is there something wrong that you think I might be doing on my end. I did copy and paste this code into C7 for the 7th row and it didn't work.

 

[shyy]

Strange works for me and ISERROR is excel 2003 compatible.

What error does your cell show?

Excel 2010

	A	B	C
7	johnsmith1882	johnsmith	johnsmith

<TBODY>
</TBODY>

Sheet4​

Worksheet Formulas Cell Formula C7 =IF(IF(ISERROR(SEARCH("*"&B7&"*",A7)),"",SEARCH("*"&B7&"*",A7))=1,B7,"") <TBODY> </TBODY>

<TBODY>
</TBODY>

 

[mikelopez]

The error that it shows me is that when I copied and pasted the input you gave me just now. Nothing shows up in cell C7. I don't know why.

 

[shyy]

Try

=IF(IF(ISERROR(SEARCH("*"&B7&"*",A7)),"TEST",SEARCH("*"&B7&"*",A7))=1,B7,"TEST")

Does TEST at lease show up instead of nothing?

on your data set, do a =TRIM(CLEAN(cell)) for all your cells, you might have some hidden spaces.

 

[mikelopez]

Good morning. Sorry to keep bothering you like this but to answer your question yes TEST at least shows up instead of nothing.

How do I do the =TRIM(CLEAN(cell))) for my cells? Do I highlight the cell C7 and then do the command.

 

[shyy]

Create a new column next to the lookup values. So if the lookup value is in column A. In any other column type =TRIM(CLEAN(A1) and drag down. Thsi will clean out the cells in column A that might have trailing spaces or hidden spaces.

Excel 2010
	A	B
1	dsafdsf	dsafdsf
2	dsafdsf	dsafdsf
3	sdaf	sdaf
4	sdaf	sdaf
5	dsafdsf	dsafdsf
6	f	f
7	asdf	asdf
8	dsafdsf	dsafdsf
9	f	f
10	sdf	sdf
11	fsd	fsd
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(CLEAN(A1))
B2	=TRIM(CLEAN(A2))
B3	=TRIM(CLEAN(A3))
B4	=TRIM(CLEAN(A4))
B5	=TRIM(CLEAN(A5))
B6	=TRIM(CLEAN(A6))
B7	=TRIM(CLEAN(A7))
B8	=TRIM(CLEAN(A8))
B9	=TRIM(CLEAN(A9))
B10	=TRIM(CLEAN(A10))
B11	=TRIM(CLEAN(A11))

 

[mikelopez]

Once this is done do I then delete what is in column A and use what is in Column B as what is being looked up or do I leave it the same and use the inverted cell formula you told me about all over again?

 

[mikelopez]

I did what you told me to do and cleaned up the cells and it still didn't work.