Converting values in one list from values in another list

split-cane

New Member
Joined
Oct 13, 2006
Messages
3
Hi there!

I have two systems at work that use different names for the same people. One uses the full name, the other an abbreviation. On one worksheet column A lists the abbreviated name and column B lists the full name. This is my "conversion" worksheet.

On another worksheet I have a column D, which has multiple instances of the abbreviated names. I want to automatically convert all of those multiple instances of the abbreviated names into multiple instances of the correct full name.

Example of conversion worksheet:

Steve Smith SteSmi
Chris Jones ChrJon
Bob Wells BobWel

Example of data list:

SteSmi ?
SteSmi ?
SteSmi ?
SteSmi ?
ChrJon ?
SteSmi ?
ChrJon ?
BobWel ?
SteSmi ?
SteSmi ?
SteSmi ?
BobWel ?
SteSmi ?

Looking for a formula that can fill in the ? with the correct full name...

Thanks,

split-cane
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

dcnick

Board Regular
Joined
May 5, 2003
Messages
62
Look in Excel Help for VLOOKUP. Here's a little to give you an idea, and this is based on the abbreviations being column A (switch your columns A and B). If your first abbreviation in your other worksheet is in cell D1, then in cell E1, type

=VLOOKUP(D1,A:B,2,FALSE)

D1 is the cell where your abbreviation is
A:B references your conversion worksheet
2 references you want to return the value in the 2nd column (your column B)
False means your original list doesn't have to be sorted

Hope this helps
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,797
Office Version
  1. 365
Platform
  1. Windows
split-cane

Welcome to the Mr Excel board!

1. I am not sure you have given us the name of the "conversion" sheet, but let's assume it is in fact called "conversion" and that your other sheet is called "data"
2. You stated that on the conversion sheet,
column A lists the abbreviated name and column B lists the full name
but your sample data has the full name to the left of the abbreviated name. My assumption is that your sample is the way you actually have it (that is, full name in column A and abbreviated name in column B)

Below are samples of both sheets. The formula in "data" sheet cell B2 (copied down):
=INDEX(conversion!$A$1:$A$10,MATCH(A2,conversion!$B$1:$B$10,0))
Mr Excel.xls
ABCD
1Full NameAbbreviated
2Steve SmithSteSmi
3Chris JonesChrJon
4Bob WellsBobWel
5
conversion
Mr Excel.xls
ABCD
1AbbreviatedFull Name
2SteSmiSteve Smith
3SteSmiSteve Smith
4SteSmiSteve Smith
5SteSmiSteve Smith
6ChrJonChris Jones
7SteSmiSteve Smith
8ChrJonChris Jones
9BobWelBob Wells
10SteSmiSteve Smith
11SteSmiSteve Smith
12SteSmiSteve Smith
13BobWelBob Wells
14SteSmiSteve Smith
15
data
 

Watch MrExcel Video

Forum statistics

Threads
1,113,795
Messages
5,544,332
Members
410,603
Latest member
rseckler
Top