Find and remplace name to id

[maverikUy]

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?

[#2 · Accepted solution]

@maverikUy 

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.