Forum Discussion
Find and remplace name to id
hi everyone
I have an excel sheet that have names of the employees, age, phone...
and I have another excel sheet whit the IDs and names of the employees.
what I need is to replace the names of the first sheet with the IDs of those employees
how i can do this?
Let's say the employee names are in A2 and down on the first sheet.
The second sheet is named Sheet 2, and it has IDs in A2:A50 and names in B2:B50.
Insert an empty column in column B on the first sheet.
Enter the word ID in B1.
Enter the following formula in B2:
=XLOOKUP(A2, 'Sheet 2'!$B$2:$B$50, 'Sheet 2'!$A$2:$A$50, "-")
and fill down.
If you don't have Microsoft 365 or Office 2021, use
=IFERROR(INDEX('Sheet 2'!$A$2:$A$50, MATCH(A2, 'Sheet 2'!$B$2:$B$50, 0), "-")
Select column B.
Copy it, then paste as values.
You can now delete column A.
1 Reply
Let's say the employee names are in A2 and down on the first sheet.
The second sheet is named Sheet 2, and it has IDs in A2:A50 and names in B2:B50.
Insert an empty column in column B on the first sheet.
Enter the word ID in B1.
Enter the following formula in B2:
=XLOOKUP(A2, 'Sheet 2'!$B$2:$B$50, 'Sheet 2'!$A$2:$A$50, "-")
and fill down.
If you don't have Microsoft 365 or Office 2021, use
=IFERROR(INDEX('Sheet 2'!$A$2:$A$50, MATCH(A2, 'Sheet 2'!$B$2:$B$50, 0), "-")
Select column B.
Copy it, then paste as values.
You can now delete column A.