Non unique identifier - Vlookup?

[exalex]

Hello,

I am trying to pull phone numbers on an excel sheet using the vlookup function, unfortunately - there is no unique identifier that is correct. Example: one persons name may read "John Billy Doe" on the one page but on the line with numbers it reads "Doe, John" or "Billy Doe John", "John B Doe", "Mr. Doe" is there any way to look these up off each other to spit out the correct phone number?

 

[Toadstool]

Hi exalex,

Let me assume Sheet1 is the sheet where you want to pull the phone numbers and Sheet2 is the sheet with a list of names and phone numbers.

Q1: Is Sheet1 a list of names and against each you want to pull 1 number from Sheet2?
A1: Then I can't see how to do this (e.g. Sheet1 says Mr. Doe but Sheet2 may have John Doe, John Doe Jr, Billy Doe) as you can't tell which name is the best fit.
In this case I would work on changing the data in either or both sheets to have the formats match.

Q2: Does Sheet1 have a cell where you type in a name and you want a list of possible matches from Sheet2?
A2: Yes, you can do this by adding a worker column to Sheet2 and using VLOOKUP with a wildcard.

 

[Gerald Higgins]

Yes, this type of question is asked over and over again on this board.

The best way to resolve this, in my opinion, is to fix your source data.