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

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
``````
• Part # QTY Part # QTY Variance 1234 8 4584 20 0 5678 10 112 25 0 1123 25 9652 10 5 1245 10 1789 45 -5 1356 5 9652 15 0 1789 40 1234 7 1 4584 20 1245 10 0 9652 15 1356 4 1 – user267887 Oct 29 '13 at 16:28
• Can you sort columns B and D and then compute the difference? – darthbith Oct 29 '13 at 16:49

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`.