Old Parts To new parts

Ok, I have finally broken down and am asking for help...here is my issue:

I have a set up old part numbers on a material master sheet in column A (Sheet 1) along side those part numbers are the new part numbers in column B.

On sheet 2 I have a bill of materials in column A with all old part numbers that I have to assign new ones from sheet 1. Fo example:

Sheet 1
Old New
000-00391-00 4707-35000-01
000-00544-00 4707-35001-01
000-00545-00 4707-35002-01
000-00546-00 4707-35003-01
000-00676-00 4707-35004-01
000-00698-00 4707-35005-01
000-00699-00 4707-35006-01


Sheet 2
Old New
000-00391-00
000-00544-00
000-00698-00
000-00676-00
000-00546-00


So as you can see not all the parts on sheet 1 are on sheet 2 and I need to somehow match up for example part 000-00391-00 on sheet 2 to part 4707-35000-01 from sheet 1


Any Ideas?

Originally Posted by shg

Have you tried VLOOKUP?

How would i use it to match AND replace the numbers? I can get it to tell me there is a match but not have it replace it with the corresponding part from Sheet 1.

Maybe this will make things more clear. I have a parts list in a material master where old parts numbers have been converted to the new part number format. All of these parts in column A match the part in column B. For Example:

Old part 000-00391-00 is now new part 4707-35000-01

The second worksheet I have is a BOM or bill of materials. This is a sheet of old part numbers used to make something. So not all the parts on the Material Master sheet are on a BOM, but all the parts in a BOM are on the material master. I need to somehow assign the part numbers from the material master to a bunch of these BOM's. It seems VLOOKUP is only good if everything matches up from the old sheet with rows and such unless I am missing something. I have a few screenshots to maybe give yous an idea of what I am dealing with.