# Yet another multiple VLOOKUP question

#### Bloacksheep

##### New Member
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.

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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.

Replies
4
Views
307
Replies
16
Views
334
Replies
1
Views
122
Replies
1
Views
313
Replies
7
Views
315

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?

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