How to lookup data in one excel file and display it in another?

[pilotdan1985]

"How to lookup data in one excel file and display it in another?" Is the short question, and I know how to do that.

My REAL question is how do you do that with the following factors:

1. I cannot change the layout of the reference file, (ie data filter or any other)
2. The reference file changes multiple times a day.
3. I'm only interested in certain data from the reference file.
4. The reference file is not in line by line order. Its printed into an excel file from another program and it doesn't come up in the order or with the info I need. See example picture of the reference data file for what im trying to accomplish in the second file:
   

What Im trying to do eventually with this second file is create a vb form from this data and add some extra data such as, "has been made" and "date manufactured"

But i believe filtering out only the desired data for this is the first step.

Any help would be greatly appreciated.