Non unique identifier - Vlookup?

exalex

New Member
Joined
Nov 1, 2017
Messages
23
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?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
819
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,444
Messages
5,486,928
Members
407,572
Latest member
smcexcel

This Week's Hot Topics

Top