Matching up VIN numbers from two different worksheets - Vlookup?

[C.j.]

I have two spreadsheets that are out of order. Both have the same VIN number list for the most part but you cannot simply sort by VIN. I need the cost news and class codes from Book 2 pasted into Book1 with the corresponding VIN number. SO if the first VIN on book1 is 1GCNCPEX1CZ3 21685 I could ctrl+F and find 1685 and see that the code is 1499 and cost new is 17,548. So I need a formula or something that says if E7 data shows up on book2 then pull data from F7 and H7 on book2 over to book1. spreadsheets attached.

[loginjmor]

Hi -

Attached is a copy of Book1. I renamed it VIN Lookup. I used SUMPRODUCT to perform the lookup for 2 reasons:
1. The VIN's are unique ID's, so there shouldn't be duplicates. That means SUMPRODUCT will find only one match and will multiply it by the target field, returning that value.
2. SUMPRODUCT is one of the few functions that will work on a closed workbook. So, if you have Book2 closed, SUMPRODUCT will still execute the search. Other functions require Book2 to be open.

The formula I used for the Class Codes looks like this:

=SUMPRODUCT((E7='C:\Users\JCM239\AppData\Local\Temp\[Book2.xlsx]Sheet1'!$E$7:$E$136)*('C:\Users\JCM239\AppData\Local\Temp\[Book2.xlsx]Sheet1'!$F$7:$F$136))

You will have to edit the formulas to the path location where you have Book2 stored. Basically it compares cell E7 (the first VIN) to the list of VIN's in Book2. If it finds a match, it multiplies that TRUE finding (which is 1) by the Class Code, which is what it returns.

The formula is the same for the Cost New, just switching the range to multiply by to column H.

One problem in your data set I noticed is in Book1, the first VIN is 1GCNCPEX1CZ3 21685. Notice the space between the first part of the VIN and the second part. In Book2, the VIN doesn't have that space, and therefore won't be a match. You need to make sure the VIN's in both lists are EXACTLY the same.

Hope this helps.