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]

Post up your sample data

 

[mikelopez]

Okay I will as soon as possible. I just need to find it first. I think i may have found the resolution to my problem but I am not sure. I will get back to you as soon as possible so I can get your input. Thank you so much for your help. I appreciate it.

 

[mikelopez]

Here is my sample data as well as the formulas that I used.

I worked on two spreadsheets using different formulas

Here is what is on Sheet1:

edcoronado	edcoronado1234	edcoronado
michaellopez	michaellopez	michaellopez
joel	joelsmith	joel
donschmit	donschmit4523	donschmit
janet smith	janetsmith222	janetsmith
dan smith	dansmith	dansmith
johnsmith1882	johnsmith	johnsmith
eddiejohnson231	eddiejohnson	eddiejohnson
laurenconley	laurenconley08	laurenconley
joann ordell	joann ordell	joannordell
joansmith	joan smth	joansmith
jake tyler	john tyler	9999
ben gordon	ben gordon	bengordon
nick tomson321	nicktomson	nicktomson

<tbody>
</tbody>

I used the following formulas to find this data:
=IF(FuzzyPercent(A1,B1,4)>50%,A1,9999) (I used this formula to find the text data if there is text in the first cell name within the cell and if there is a name plus number in the second cell.)
=IF(FuzzyPercent(B7,A7,4)>50%,B7,9999) (I used this formula to find the text data if there is a number in the name in Cell A8 or the first cell.)
=SUBSTITUTE(A11, " ","") (I used this formula to help me find the same text if it is separated by spaces in either on or both cells.)

This includes the output.

This is the first sheet. In my next post I will post the second sheet.

 

[mikelopez]

Here is what I used for Sheet 2.

edcoronado	edcoronado1234	edcoronado
michael lopez	michaellopez	michaellopez
joel	joelsmith	joel
donschmit	donschmit4523	donschmit
janetsmith	janetsmith222	janetsmith
dansmith	dansmith	dansmith
johnsmith1882	johnsmith	johnsmith
eddiejohnson231	eddiejohnson	eddiejohnson
laurenconley	laurenconley08	laurenconley
nick tomson321	nicktomson	nicktomson
ben gordon	ben gordon	bengordon
jake tyler	john tyler	9999

<tbody>
</tbody>

=IF(NOT(ISERROR(SEARCH("*"&A1&"*",B1))), A1, "9999")
(I used this formula to find the text data if there is text in the first cell name within the cell and if there is a name plus number in the second cell.)

=IF(NOT(ISERROR(SEARCH("*"&B7&"*",A7))), B7, "")
(I used this formula to find the text data if there is a number in the name in Cell A8 or the first cell.)

=SUBSTITUTE(A12, " ","")
(I used this formula to help me find the same text if it is separated by spaces in either on or both cells.)

=IF(NOT(ISERROR(SEARCH("*"&A14&"*",B14))), A14, "9999")
(I put 9999 in quotation marks to have the user see those numbers if the names don't match.

I like the formulas that I used but do you think there is a universal formula that I could use that would do all of these functions above without having me to switch between A and B in the cell placement on the formula?

If you don't know, that's okay. I understand. I greatly appreciate your help. Thank you again. What do you think?

 

[mikelopez]

One more thing I forgot to mention, I reduced the security for macros to low in excel 2003 so that I could use that FUZZYLOOKUP function.

 

[shyy]

You are better off using a macro, hopefully someone can come up with what you want. Also check out AND and OR functions. good luck.