Yet another multiple VLOOKUP question

Bloacksheep

New Member
Joined
Feb 3, 2009
Messages
3
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?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
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?
 
Upvote 0

Bloacksheep

New Member
Joined
Feb 3, 2009
Messages
3
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
 
Upvote 0

Ragnar1211

Well-known Member
Joined
Jul 10, 2008
Messages
571
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.
 
Upvote 0

Forum statistics

Threads
1,190,696
Messages
5,982,343
Members
439,775
Latest member
mathewduffy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top