Replacing multiple values with multiple values

**mom2boys** · 05-13-2011, 08:41 AM

I need to do a complicated version of find & replace involving two spreadsheets. The first spreadsheet has 2 columns with multiple instances of employee names and numbers:
# Name
1 John Doe
2 Jane Smith
2 Jane Smith
2 Jane Smith
3 Joe Blow
3 Joe Blow

The second spreadsheet has only single instances of employee names and CORRECT numbers:
# Name
123 John Doe
456 Jane Smith
789 Joe Blow

What I need to do is replace the "#" column in the first spreadsheet, with the correct numbers from the "#" column in the second spreadsheet, so it would end up like this:
# Name
123 John Doe
456 Jane Smith
456 Jane Smith
456 Jane Smith
789 Joe Blow
789 Joe Blow

There are over a thousand employees so hoping to not have to do this by hand. Any ideas?

**NBVC** · 05-13-2011, 08:48 AM

In a 3rd column, you can use a Index/Match lookup to get correct ID.

e.g.

=INDEX('Sheet2'!A:A,MATCH(B2,'Sheet2'!B:B,0))

where B2 contains first name in sheet1.

copy formula down.

Then you can copy this column and paste special >> Values over the original column A, then delete the formula column.

**Cutter** · 05-13-2011, 08:48 AM

You should be able to do it with INDEX() and MATCH().

Where you have the numbers on sheet 1 (I'll assume it begins at A2) place this formula:

=INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0))

and copy down.

This assumes numbers and names are in A and B columns of sheet 2.