Vlookup - partial and reversed text string

[tpf1001]

I'm trying to lookup a person's name and return an ID number (Spreadsheet 1) from another spreadsheet that has the name and ID number (Spreadsheet 2). However, the names in Spreadsheet 1 may only contain either partial text, or contain more text, slightly different text, than what is in the name column in Spreadsheet 2. I've tried using partial text vloookup, as well as other match, index-match, and other vlookup functions.

Ex.)

Spreadsheet 1
- I have a list of around 500 names that are entered into individual cells in the form (Last Name, First Name) or (Company Name); sometimes the first name or the last name, or both the first and last name, will be in the same cell as the company name (e.g., Arcane Builders Co. - Bob and Cheryl)
- I have tried taking that list of 500 names/companies and removing text that includes: spaces, commas, ampersands, etc. (e.g., Smith, Michael --> SmithMichael; Arcane Builders Co. - Bob and Cheryl --> ArcaneBuildersCoBobCheryl)

Spreadsheet 2
- I have 2 columns that correspond to around 16,500 names (Column A) with respective number IDs (Column B)
- I have also removed all spaces, commas, etc. in the name column in this spreadsheet


Spreadsheet 1

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Company/Name[/TD]
[TD]Number_ID[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]SmithMichael[/TD]
[TD]=vlookup(...[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]JonesSarah[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ArcaneBuildersCoBobCheryl[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Spreadsheet 2

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Company/Name[/TD]
[TD]Number_ID[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]MichaelSmith[/TD]
[TD]97362[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]SarahJones[/TD]
[TD]34556[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ArcaneBuildersCo[/TD]
[TD]56712[/TD]
[/TR]
</tbody>[/TABLE]




- I have tried running a partial text vlookup, but I think I'm not returning nearly as many number IDs because the names are often written in reverse order in the 2 spreadsheets

=vlookup("*"&A2&"*",[Book1.xlsx]Sheet2!$A$2:$B$16464,2,false)

- This function is bringing back a lot of #NA when I know they are in the other spreadsheet, just written in reverse order

- I have also tried running Index-match functions and still no good (Not sure If I'm doing it right...)

Any suggestions?

 

[MixedUpExcel]

Hi tpf1001,

Here's something that you might be able to try based on what you've put above.

If you have a vlookup but just looking for the first 3 letters of the name in Sheet1 against the list of names in Sheet2

=VLOOKUP("*"&LEFT(A1,3)&"*",Sheet3!A1:A3,1,0)

but to make sure you retrieve all but not more than you expect - you could put a countif in the column next to it?

=COUNTIF(Sheet3!A:A,"*"&LEFT(A1,3)&"*")

I'd even thought about using either AND or OR in your vlookup (comprising of 2 x vlookup's) to try to match criteria of first 3 letters and last 3 letters?

What do you think?

Simon

 

[Mackers]

Hi tpf1001

What I would do is go to your spreadsheet of good data (Spreadsheet2) and match back to Spreadsheet1, then look up from Spreadsheet1 to that matched column in Spreadsheet2 and return the ID if you found a match.

First use a function to split the Spreadsheet2 names into separate words (plenty of solutions are on this site if you search). Then, in each row on Spreadsheet2 look up "*"&Word1&"*"&Word2&"*"&"Word3"&"* against a new column you add to Spreadsheet1 that is the badly-formatted name repeated three times (or as many times as there are maximum words, minus one). This is similar to your technique but we are working backwards (Spreadsheet2 to Spreadsheet1) rather than forwards.

So for example, on Spreadsheet1 you have a cell that says "Smith Michael" as the original name, a cell next to it that says "Smith Michael Smith Michael Smith Michael" from repeating the cell (use the REPT() function or =A2&A2&A2 or whatever), and then on Spreadsheet2 you are doing a MATCH("*Michael*Smith**", YourSpreadsheet1RepeatedNames, 0). You then need to look up from Spreadsheet1 to Spreadsheet2 to see for each row if the MATCH() is present, and if so return the appropriate ID.

Hope that helps - it's not an ideal solution but should work for most cases. Let me know if the above is confusing for you, it's not very well written, sorry!

Mackers