Combine two lists while matching column values

Hello Guys and Gals,

I've been a silent but heavy user of the knowledge on Excel Forum for a while now but have run aground on this one.

I have two lists of uncounted inventory (developing a 5 year trend so to speak):

List 1: 5 columns: 4 columns of product numbers, 1 column of descriptions
The 4 columns of product numbers differ in filled values but are all aligned with each other at the moment ie A: 111, 111, 111,111 B: 222, (blank), 222, 222

List 2: 2 columns: 1 column of product numbers, 1 column of descriptions

List 2 contains more values than list 1. List 2 also contains SOME but not ALL of the values from List 1.

I need to combine both lists by adding column 2's product numbers, having them match up with the descriptions and product numbers in all the other cells of list 1 while inserting new lines for product numbers that dont exist in List 1 at all.

I have attached a short example. Any help would be appreciated as going through 42,000 part numbers individually would wreck my ... month!

My example has 3 sheets included fyi.

Help(2sheets).xlsx

Thank you for the quick reply MarvinP but I am a bit lost on the solution you posted.

In the new column you added I now have part numbers that do not match up with the descriptions or the other part numbers. What I am hoping to get close to is what is on the "Result" sheet.
Instead I have 3 repeated "battery" part numbers where other values should be and a blank where the actual battery part number should be.

MarvinP,

I used a vlookup formula and figured it out.

Thank you!