Yet another multiple VLOOKUP question

[Bloacksheep]

Hi folks,

I work in a manufacturing company where all parts have an assigned part number (basically treated as text) and a cost. We update part costs on some but not all parts every three months. The lists only contain that parts that were quoted that quarter. Every quarter, I need to determine the costs of our products. So I take the parts list for each product and I need to look in all three lists and grab the most recent quote for each part in the products. I can then sum the costs together for the parts in each product.

So I now have three lists that have part cost quotes for the last three quarters (no further back). If there are three quotes, I take the most recent quote. If there is only one quote and it is three quarters back, I use that quote. Essentially, the most recent quote has precedence over all others. The quotes are on separate worksheets.

I am struggling with the best way to do this. I have a set of array function that appear to be doing what I want, but they appear pretty ugly. Basically, I use three columns to check for a part quote in each of the three lists (I trap the NAs and make them 0). Then I use a fourth column to pick the most recent non-zero quote of the three columns.

It seems like a complicated solution. Any ideas for a simpler one?

 

[Sal Paradise]

Why not just keep a single master sheet, and then add a column every month that looks for that part number in the latest file, or takes the old number if there is no update?

 

[Bloacksheep]

Hi Sal,

I was thinking of creating a consolidated costing sheet. This has proven to be more time consuming that I thought. The sheet that purchasing sends me are enormous and complex. I was trying to to see if there was someway I could use their sheets without modification.

Thanks for the suggestion. I will start to look at this option more closely.

Zig

 

[Ragnar1211]

Similar to what Sal has said:

1) Can you put the lists in the same table? Add a helper column to designate the quarter.

2) Sort the list with newest dates at the top, oldest at the bottom.

3) Add a helper column to countif the product code, anchoring the first cell in the countif like: =countif($b$2:B2,c2) and fill down.

4) Then you can use sumproduct to get the 1st result for each product type you are after.