conditional vlookup help

[jack]

In need of some excel expertise!:

I have two tables:

Table 1
Part #, Quantity

Table 2
Project #, Part #, Quantity for Project


I want to create a 3rd table that combines the above tables. Table 1
will act as the master and I will continue to add projects to Table 2
(each time that a project is added to table #2, I want for that part
#'s quantity to subtract from the respective quantity on table #1).

I have tried creating a consolidated pivot table, but it did not work
consistently. Can someone please advise as how to create a vlookup to
achieve this?

Much thanks!

 

[Guest]

Here's a suggestion, but it doesn't involve vlookup and uses an added column
in Table 1 (Original Quantity).

Say Table 1 is in A2:C50 (A=Part #, B=Orig Qty, C=Remaining Qty) and Table 2
is in F2:H25 (F=Project #, G=Part #, H=Qty).

Then make C2=B2-SUMIF(G$2:G$25,A2,H$2:H$25) and copy this down to C3 thru C50.

Will