Excel difference between cells in two tables

I want to compare two tables in Excel 2010, and开发者_如何学编程 see the differences in the numeric cells in a third table?

Here is an example of the initial tables:

table 1
name    value   category
aa      1.0     red

table 2
name    category value
bb      blue     1.0

---

The caveats for this formula are

• tables are named ranges, table1 and table2
• destination column has label in Row 1, values starting in Row 2

You'll have to modify the ROW() part based on your actual placement. You can place it any way you want, you just have to change the ROW() part to give numbers starting at 2 and incrementing by 1.

=HLOOKUP("value",table1,ROW(),FALSE)-HLOOKUP("value",table2,ROW(),FALSE)

If you don't want zero value differences to show, you can either set the workbook to not show zeroes or mod the formula this way:

=IF(HLOOKUP("value",table1,ROW(),FALSE)-HLOOKUP("value",table2,ROW(),FALSE) > 0,
    HLOOKUP("value",table1,ROW(),FALSE)-HLOOKUP("value",table2,ROW(),FALSE),"")