How to compare two lists and note the differences between values?

We want to compare List One and  List Two against each other  in terms of  actual sales and estimated sales.



If they are the same then identify the records as "Identical", if the record doesn't exist in the other list then put "NA" and if the record exists and are not the same then calculate the  difference between the two values.

Firstly if   record exists then either it is  the same or the difference is noted:

This  Vlookup function searches for    SKU 1001 in the table located at F19:G24. If it returns the  same value as whats in C19, then the IF  function returns  'Same'  or it returns the difference.


But we also need to find out if the value exists or if it dos not. this is done usig this formula:


This Match function  looks for ' 1001 ' in the column range F19:F24. If it finds 1001, it will returns it's  relative row position ,if not it returns N/A


Now bringing it all together, The formula in D19 is



If  the Match Function  can't find a match For '1001' ,it returns  N/A.

This makes the ISNA Function  return True, so then the  IF  function will return ''# N/A' and finish.     If the MATCH function returns a  relative row number, The ISNA  function returns False, and the  IF function  starts the false part  i.e

IF(C19=VLOOKUP(B19,$F$19:$G$24,2,0),"Identical",C19-VLOOKUP(B19,$F$19:$G$24,2,0)  as described in Step one.

Note: The ISNA Funtion  returns True is it receives N/A as its argument result.