MS Excel - Find cell value in column, then determine value difference of cells

Question

I need to compare to columns to determine if a part number (distinct) is in both columns, then determine the variance in number of parts. In this example, I'd like to have Column D find it's distinct part number in Column B, then subtract the Qty found in Column E from the Qty found in Column C to determine the Variance (Column F).

A       B          C               D         E          F
1    Part #       QTY           Part #      QTY       Variance   
2     1234        8              4584        20         0
3     5678        10             1123        25         0
4     1123        25             9652        10         5
5     1245        10             1789        45        (5)
6     1356        5              9652        15         0
7     1789        40             1234        7          1
8     4584        20             1245        10         0 
9     9652        15             1356        4          1

Answer 1

Based on this site: http://office.microsoft.com/en-us/excel-help/look-up-values-in-a-range-HP005228952.aspx

You can place the following formula in cell F1 and fill it down column F:

=INDEX(C$1:C$8,MATCH(D1,B$1:B$8,0))-E1

This function does several things. The first is the MATCH part. MATCH tells Excel to find the value in cell D1 somewhere in the range B1:B8. The $ symbol before the numbers is so when you copy the cell to the rest of the column, it still matches the proper rows. MATCH returns the position of the value in B1:B8 that matches D1. Because the third input to MATCH is 0, it looks for the exact value. Next is the INDEX, which returns the value of a given row from a given column. Here, the column is given by C1:C8, because these are the values we're interested in operating with. The row number is provided by the MATCH function. Finally, we subtract the value in column E.