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:

New Employee ID	Old Employee ID
ST000001	00402
ST000002	00511
ST000003	00108
ST000004	00073

<tbody>
</tbody>

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:

Old Employee Number	Dependent type	Insurance Class
00402	Wife	A+
00402	Son	A
00402	Daughter	A
00511	Wife	A+
00073	Son	B

<tbody>
</tbody>

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:

New Employee ID	Old Employee ID	Dependent type	Insurance Class
ST000001	00402	Wife	A+
ST000001	00402	Son	A
ST000001	00402	Daughter	A
ST000002	00511	Wife	A+
ST000004	00073	Son	B

<tbody>
</tbody>

I hope I was able to clarify my request,

Thank you and appreciate your help

 

[Kamolga]

	A	B
1	New Employee ID	Old Employee ID
2	ST000001	402
3	ST000002	511
4	ST000003	108
5	ST000004	73

<tbody>
</tbody>

Sheet1

	A	B	C	D
1	Old Employee Number	Dependent type	Insurance Class	New Employee Number
2	402	Wife	A+	ST000001
3	402	Son	A	ST000001
4	402	Daughter	A	ST000001
5	511	Wife	A+	ST000002
6	73	Son	B	ST000004

<tbody>
</tbody>

Sheet2

Worksheet Formulas Sheet2 Cell Formula D2 =INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A2,Sheet1!$B$1:$B$5,0),1) D3 =INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A3,Sheet1!$B$1:$B$5,0),1) D4 =INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A4,Sheet1!$B$1:$B$5,0),1) D5 =INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A5,Sheet1!$B$1:$B$5,0),1) D6 =INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A6,Sheet1!$B$1:$B$5,0),1) <tbody> </tbody>

<tbody>
</tbody>

 

[sim_soul]

	A	B
1	New Employee ID	Old Employee ID
2	ST000001	402
3	ST000002	511
4	ST000003	108
5	ST000004	73

<tbody>
</tbody>

Sheet1

	A	B	C	D
1	Old Employee Number	Dependent type	Insurance Class	New Employee Number
2	402	Wife	A+	ST000001
3	402	Son	A	ST000001
4	402	Daughter	A	ST000001
5	511	Wife	A+	ST000002
6	73	Son	B	ST000004

<tbody>
</tbody>

Sheet2

Worksheet Formulas Sheet2 Cell Formula D2 =INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A2,Sheet1!$B$1:$B$5,0),1) D3 =INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A3,Sheet1!$B$1:$B$5,0),1) D4 =INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A4,Sheet1!$B$1:$B$5,0),1) D5 =INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A5,Sheet1!$B$1:$B$5,0),1) D6 =INDEX(Sheet1!$A$1:$B$5,MATCH(Sheet2!A6,Sheet1!$B$1:$B$5,0),1) <tbody> </tbody>

<tbody>
</tbody>

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