# Converting values in one list from values in another list

#### split-cane

##### New Member
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

### 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
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
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

Replies
18
Views
119
Replies
3
Views
72
Replies
5
Views
137
Replies
2
Views
97
Replies
0
Views
12