I need to compare two tables and return the differences.

Yes it will note the changes and return False for the two rows C 30 and E 80.

Having marked all changed rows with FALSE, you can either SORT Them, or FILTER them to get the list of FALSE entries together. Or you can use conditional formatting to highlight the changed rows in different Colour.

Let me explain the formula in detail to clarify the concept.

=IFERROR(VLOOKUP($A2,Sheet1!$A$2:$C$5,2,FALSE)=B2,FALSE)

Let us first look at the inner part of the formula which is:

VLOOKUP($A2,Sheet1!$A$2:$C$5,2,FALSE)


What VLOOKUP does in effect is that, it searches for the Key in $A2 of Sheet 2 in the first column of the Table in Sheet 1 ($A$2:$C$5) and if it finds a matching key there it returns the data in 2nd column of the Table (i.e. the sales column), The FALSE stands for an "EXACT MATCH" (instead of a TRUE which stands for nearest match).

So the expected returned values of this VLOOKUP function will be as follows:

Key Sales Compare
A    10                10
B    20                20
C    30              #N/A
D    40                40
E    80                 50


Note that since the system is unable to find "C" in the First Sheet, it returns a #N/A error. Effectively all new rows will be marked with #N/A errors.

Now the task boils down to compare the figures in column B of Sheet 2 which is the Current Sales vs. the Previous Sales which have come using the VLOOKUP formula.

So, if we just use the equation:

=VLOOKUP($A2,Sheet1!$A$2:$C$5,2,FALSE)=B2

it returns TRUE for all cases, where the sales remain the same.
it returns FALSE for all cases where the sales column varies.
it returns #N/A for all cases where the comparison is done for an #N/A field

Key Sales Compare
A    10                TRUE
B    20                TRUE
C    30               #N/A
D    40                TRUE
E    80               FALSE

Finally to remove the error, we forcibly change all errors to FALSE by using the IFERROR function.

Hence the final Formula.

=IFERROR(VLOOKUP($A2,Sheet1!$A$2:$C$5,2,FALSE)=B2,FALSE)

results in :

Key Sales Compare
A    10                TRUE
B    20                TRUE
C    30              FALSE
D    40                TRUE
E    80               FALSE


Please note that VLOOKUP does not match ROW-BY-ROW, it searches the KEY in the next sheet from TOP to BOTTOM (hence the V for VERTICAL in VLOOKUP) till it finds a Match.

Hope it is clear.