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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

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
47,479
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,118,186
Messages
5,570,749
Members
412,339
Latest member
sstackho
Top