I scavenged the forums a bit to see if anyone had solved a problem similar to mine. A lot were very similar but the solutions were long and drawn out and I feel like it should be quick.

-First let me say that I have already solved this problem in a basic way (As in I can use multiple formulas over a few sheets to solve it) However I feel it can be pushed into a single formula and be re-used.

We have two procurement lists. List A has all the items that are supposed to bought with the quantities associated, the parts are split by numbers and names. However List B is the list of items that have been bought, they are also split by numbers and names but the names may have changed due to the vendors descriptions(List B may also contain parts not on List A that need to be avoided). Therefor we have 2 lists with matching numbers/quantities ordered.

What I did was compare the lists to find numbers that matched and had the columns match up with quantities next to one and another and then did a difference column on the quantities to see if we still needed to order any more parts.

Example Below of the Lists

List A	Qty Ordered	List B	Qty Needed
1011	15	1011	20
1012	25	1012	30
2032	22	2032	25
2110	15	2110	15
2340	10	2340	11
5300	5	5300	10

Now to be realistic my list has about 2000 parts, there are about 15 columns plus there are repeats of numbers that add on to each other. So item 1011 may also be listed again needed 10, therefor the total is 30.

My solution was to do a lookup and create a new sheet with only the numbers and quantities next to each other like the above table. I then do a difference column and create a new sheet with what needs ordered.

What I would like to do (I have been messing around with this) is to create one formula column next to LIST A in the last column that will constantly do a lookup on the item number, then compare the current quantity to LIST B and find the difference to see if more parts are needed. It feels like a simple idea but I can't seem to figure it out since I usually used our inventory program to do this work but we have switched to excel.

I would use a formula with SUMIF for both the quantity ordered and the quantity needed.

Make a unique list of your part numbers

1011

1012

etc.

Then, assuming the first is in cell A2, in B2 use

=SUMIF(SheetOrdered!A:A,A2,SheetOrdered!B:B)

and in C2

=SUMIF(SheetNeeded!A:A,A2,SheetNeeded!B:B)

Thank you this is kind of what I was doing but worked a little better. 

Thanks to share the helpful information to us. This will help the others who meet the same issue.