Does this excel formula exist?

[xsilver]

Hey, I'm trying to find something that can help me collaborate two excel colums into some meaningful data.

Only problem is that my name list is not exactly the same so is there a formula that searches partial matches?

eg. I know about the "MATCH" and "VLOOKUP" function but that only works if two cells are identical

so what I have is for example
Data set A:
Bill Gates = 1
Steve Jobs = 2
Michael Jackson = 3

Data set B:
Bill Gates MSFT = $100
Jobs, Steve = $200
Michael Jackson = $300

The match/vlookup function will only work for michael jackson? Is there a function which will allow me to find the examples of bill gates of steve jobs? I have a list of 350+ names and besides editing them by hand to make them the same (PAIN IN THE ASS) I don't know any other way.
Even something that can tell me its one of 3 possible matches (if there are conflicts) would help a lot)

thanks in advance.

 

[xsilver]

ok - to answer my own question - I think I have found the answer
http://www.mrexcel.com/forum/s...ad.php?p=331454#338580

only problem is - I dont really understand it!
I need someone to explain it in laymans terms lol. (i've never used a UDF before)


For my specific need I have a list with names that may not match due to a comma in the name, a suffix on the end of the name or the name enclosed in [ ] brackets. I dont actually need such advanced functions as ranking %'s of matching. I also dont think I will have duplicates; eg. from my example above, I will probably only have Bill Gates MSFT or Bill Gates. or Bill, Gates or [Bill Gates] worst case scenario being [Gates, Bill MSFT]

 

[mayest]

I don't know that you need a user-defined function. Maybe, depending on your exact needs. Here are a couple of ideas to look at before going the UDF route:

1) The Match() function can make use of wildcards (? and * only) for strings. It is far from a full regular expression, but it might work for you.
2) Perhaps you can use a Filter? Excel can easily filter a list, and you can define your criteria and even filter multiple columns.
3) Make use of the various string (text) functions to clean up your data before doing your VLookup() or Match() functions. You can also use these string functions (e.g., Left(), Mid(), Right(), etc) within your VLookup() or Match() functions.

 

[tdawg]

So, I used the following function to check if a line of text contained certain text.

=IF(ISNUMBER(SEARCH("TAX",G9)),1,0)

In this case, I was looking through column G for any row that had "TAX" written in it and wrote a '1' in column E if "TAX" appeared in the row, or a '0' if it didn't. Perhaps you can adapt this to your purpose, maybe even a conversion of the names field (i.e. IF(ISNUMBER(SEARCH("GATES",B2)),"Bill Gates",0)). With so many names to translate, it might be cumbersome, though.

P.S. I don't know why ISNUMBER is required, but the formula would not work without it, even though I wasn't searching for numbers and the column being searched was general text.

 

[xsilver]

Unless Im understanding it wrong - I dont think the wildcards can help because my list of names is not fixed in the number of characters?
eg. =Match("B2*", A2:A5, 0) Doesnt link to cell B2? - it doesnt work without the "" either
It should work if I ask =Match("Bill*", A2:A5, 0) but that only works for 1 cell. The cell below it is going to search for steve jobs so I cant use text in the search, I need to use B2 or at least the first 8 characters of B2.

also im not sure what you propose with the filter function? filter doesnt work without exact matches?

The left function sounds promising but for some reason I'm getting a #value error?
=MATCH(LEFT(B2,8),LEFT($D$2:$D$101,8),0)
Is there something wrong with this formula? This matches the first 8 characters?


Im not sure the =IF(ISNUMBER(SEARCH("TAX",G9)),1,0) function is going to work because the subject "tax" has to be entered manually? it works for 1 cell, but not when you have 300+ to lookup and compare. eg. I need to be able to ask it to search for say the first 8 characters in a cell and compare that to a list and tell me that D22 is the same (or close enough) as B2

 

[xsilver]

BUMP - also is there a way to remove a suffix from a list of cells
eg. Bill Gates MSFT turns into Bill Gates - I wont know the number of characters in the name, but I should know the number of characters that need to be deleted.