Combining data between two sheets

[EwatchesUSA]

Hello everyone, I have excel wordbook with two sheets in it,Sheet A has all our auto parts, SKU, quantity, purchase date, and Item cost, sheet B contain, SKU, PURCHASE DATE, and Item cost, when we receive new parts we add theme to sheet B and sheet A gets updated automatically, sometimes when we receive new price change we have to look in sheet A one by one for the parts with new price and we change it manually, I'm looking for formula to put in sheet A to pull in the newest price added in sheet B, kEEP IN MIND SHEET B can have the same part number multiple times, I want pull in only the last price update.

Regards Mike

 

[sijpie]

A nice challenge, but here is the answer.
Let's assume that on Sheet B you have the following rangenames:
SKU for the column with the SKU (it is best not to have the whole column, as that will slow down the sheet significantly).
PDate for the column with PurchaseDate (as above)
ICost for the column with Item Cost (again)

OK. In sheet A, let's assume that the SKU is in column B, starting from B3, and the ItemCost in column E (from E3).
This last one you want to have the formula to pull the latest price from sheet B.

So the formula in E3 is:
=INDEX(ICost,SUMPRODUCT(MAX((SKU=B3)*ROW(SKU)))-ROW(PDate)+1)

For an explanation how it works: Use SUMPRODUCT to Find The Last Item in an Excel List