New employee numbers instead of the old numbers

[sim_soul]

Hi Everyone,

I am not an expert in Excel, and I have been trying to figure out how I can do this, but couldnt get to the right formula, I hope I can get the solution here.

The Case:

I have an Excel workbook which has multiple sheets of HR Information. We decided to stop dealing with the current Employee numbers and created a new Employee ID for each employee.

To do that, in the master sheet, I created a new column with new Employee IDs to replace the Older ones as the below example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]New Employee ID[/TD]
[TD]Old Employee ID[/TD]
[/TR]
[TR]
[TD]ST000001[/TD]
[TD]00402[/TD]
[/TR]
[TR]
[TD]ST000002[/TD]
[TD]00511[/TD]
[/TR]
[TR]
[TD]ST000003[/TD]
[TD]00108[/TD]
[/TR]
[TR]
[TD]ST000004[/TD]
[TD]00073[/TD]
[/TR]
</tbody>[/TABLE]

Now, I would like to replace any Old Employee ID in the other sheets with the new corresponding Employee ID as given in the master table above,

For example, the current dependents Insurance sheet looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Old Employee Number[/TD]
[TD]Dependent type[/TD]
[TD]Insurance Class[/TD]
[/TR]
[TR]
[TD]00402[/TD]
[TD]Wife[/TD]
[TD]A+[/TD]
[/TR]
[TR]
[TD]00402[/TD]
[TD]Son[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]00402[/TD]
[TD]Daughter[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]00511[/TD]
[TD]Wife[/TD]
[TD]A+[/TD]
[/TR]
[TR]
[TD]00073[/TD]
[TD]Son[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

I want to add a new column with a formula that goes to the master sheet, searches for the Old employee number and returns its corresponding New Employee Id. So the Dependents Insurance sheet should look like this instead:

[TABLE="width: 500"]
<tbody>[TR]
[TD]New Employee ID[/TD]
[TD]Old Employee ID[/TD]
[TD]Dependent type[/TD]
[TD]Insurance Class[/TD]
[/TR]
[TR]
[TD]ST000001[/TD]
[TD]00402[/TD]
[TD]Wife[/TD]
[TD]A+[/TD]
[/TR]
[TR]
[TD]ST000001[/TD]
[TD]00402[/TD]
[TD]Son[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]ST000001[/TD]
[TD]00402[/TD]
[TD]Daughter[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]ST000002[/TD]
[TD]00511[/TD]
[TD]Wife[/TD]
[TD]A+[/TD]
[/TR]
[TR]
[TD]ST000004[/TD]
[TD]00073[/TD]
[TD]Son[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]


I hope I was able to clarify my request,

Thank you and appreciate your help

 

[Kamolga]

	A	B

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]New Employee ID[/TD]
[TD="align: center"]Old Employee ID[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]ST000001[/TD]
[TD="align: center"]402[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]ST000002[/TD]
[TD="align: center"]511[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]ST000003[/TD]
[TD="align: center"]108[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]ST000004[/TD]
[TD="align: center"]73[/TD]

</tbody>

Sheet1

	A	B	C	D
Old Employee Number	Dependent type	Insurance Class	New Employee Number
Wife	A+	ST000001
Son	A	ST000001
Daughter	A	ST000001
Wife	A+	ST000002
Son	B	ST000004

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]402[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]402[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]402[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]511[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]73[/TD]

</tbody>

Sheet2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas Sheet2[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A2,Sheet1!$B$1:$B$5,0),1)[/TD]
[/TR]
[TR]
[TH]D3[/TH]
[TD="align: left"]=INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A3,Sheet1!$B$1:$B$5,0),1)[/TD]
[/TR]
[TR]
[TH]D4[/TH]
[TD="align: left"]=INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A4,Sheet1!$B$1:$B$5,0),1)[/TD]
[/TR]
[TR]
[TH]D5[/TH]
[TD="align: left"]=INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A5,Sheet1!$B$1:$B$5,0),1)[/TD]
[/TR]
[TR]
[TH]D6[/TH]
[TD="align: left"]=INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A6,Sheet1!$B$1:$B$5,0),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sim_soul]

	A	B

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]New Employee ID[/TD]
[TD="align: center"]Old Employee ID[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]ST000001[/TD]
[TD="align: center"]402[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]ST000002[/TD]
[TD="align: center"]511[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]ST000003[/TD]
[TD="align: center"]108[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]ST000004[/TD]
[TD="align: center"]73[/TD]

</tbody>

Sheet1

	A	B	C	D
Old Employee Number	Dependent type	Insurance Class	New Employee Number
Wife	A+	ST000001
Son	A	ST000001
Daughter	A	ST000001
Wife	A+	ST000002
Son	B	ST000004

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]402[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]402[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]402[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]511[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]73[/TD]

</tbody>

Sheet2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas Sheet2[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A2,Sheet1!$B$1:$B$5,0),1)[/TD]
[/TR]
[TR]
[TH]D3[/TH]
[TD="align: left"]=INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A3,Sheet1!$B$1:$B$5,0),1)[/TD]
[/TR]
[TR]
[TH]D4[/TH]
[TD="align: left"]=INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A4,Sheet1!$B$1:$B$5,0),1)[/TD]
[/TR]
[TR]
[TH]D5[/TH]
[TD="align: left"]=INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A5,Sheet1!$B$1:$B$5,0),1)[/TD]
[/TR]
[TR]
[TH]D6[/TH]
[TD="align: left"]=INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A6,Sheet1!$B$1:$B$5,0),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks alot Kamolga, however This seems to work perfectly well if I am using the cell is formatted as a number, but if the cell is formatted as text i.e the Old Employee number is written like this "00402" it doesnt work... Can this work with strings?

 

[Kamolga]

Of course, they need to be both formated as text then. Just be careful that you don't have some with a space (you can use trim() to take them out) at begining or the end or some starting with '. If it does not work, simply put value() to work with numbers or text() to work with strings