Find and remplace name to id

Occasional Visitor

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?

1 Reply
best response confirmed by maverikUy (Occasional Visitor)


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.