Converting values in one list from values in another list

[split-cane]

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

 

[dcnick]

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]

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
	A	B	C	D
1	Full Name	Abbreviated
2	Steve Smith	SteSmi
3	Chris Jones	ChrJon
4	Bob Wells	BobWel
5
conversion

Mr Excel.xls
	A	B	C	D
1	Abbreviated	Full Name
2	SteSmi	Steve Smith
3	SteSmi	Steve Smith
4	SteSmi	Steve Smith
5	SteSmi	Steve Smith
6	ChrJon	Chris Jones
7	SteSmi	Steve Smith
8	ChrJon	Chris Jones
9	BobWel	Bob Wells
10	SteSmi	Steve Smith
11	SteSmi	Steve Smith
12	SteSmi	Steve Smith
13	BobWel	Bob Wells
14	SteSmi	Steve Smith
15
data