Find or Match and Replace-large data base

Hello,
I have data in three tabs. File is attached.

In Tab 1, I would like to look up the unit price from Tab 2.
Problem 1: Part number in Tab 1 does not match to Part number in Tab 2
Problem 2: Some of the part numbers had changes which are cross referenced in Tab 3.

Solution – In Tab 2, the orange highlighted columns are data that is the system. The green highlighted columns are my versions of creating a part number column that I can match to for Tab 1.

Tab 2 - Column A has errors. I am trying to find the first 7 numbers of Part Number-C to the New number (Col A) on Tab 3 and if it matches, then replace it with the old part number (Col B). It’s not working.

Until I can fix the find and replace part number, I have taken Col B’s Part number on Tab 2 and indexed the unit price to put in Tab 1, Column I. If there is an error, then it will look at the PO number to find the unit price on Tab 2. This is still not the perfect solution because I may still have to look at some parts case by case.

Is there a way to find the part number from Tab 1 to Tab 2’s column G without having to extract certain data?

Thanks for all the help I can get.

Hi Jacc,

First, thanks for all the effort you put to help me.

Let me work with the formulas you have provided. They are good, but it may take me some time to understand it all. There are too many issues with what I am trying to accomplish because the part numbers were entered two different ways in our system. Therefore, this may not work fully. I was hoping to help out a colleague by creating formulas to lookup the unit price vs. looking each one up manually. In this case, we had to manually come up with the unit price and now I am trying to see if I can duplicate the unit prices by using formulas. I noticed there are still some other issues like some part numbers have (MCDC) or (MCDB) at the end and so may have some parts that are case by case.

For now, I am going to mark this solved and if I have questions, I will re-post.

Thanks again for all your help!!!