Excel - Comparing columns from different sheets

November 2016


I have column A in datasheet 1 which has a list of names.
In Column A of datasheet 2 I have a much longer list of names. Some of the names are in both columns from both sheets. I am looking for a formula that will produce in column A of datasheet 3 the names that appear only in sheet 2.


In sheet2 in B2 (row one is column headings in both sheets)
  • Type this formula
    • =IF(ISERROR(A2=Sheet1!$A$2:$A$10),"NO","YES") 
  • While writing the formula take the cursor to sheet1 and draw it whole data range in column A so that

$A$2:$A$10 will be correct range.
  • convert these references to absolute ref.
  • Copy B2 down .
  • The easy way is take the cursor to right bottom B2 when cursor changes into bold plus sign.
  • Click on the + sign.
  • Now filter sheet according to column B criteria being "NO" .
  • In the filtered data copy column A and paste it in sheet 3.

Thanks to Venkat1926 for this tip.

Related :

This document entitled « Excel - Comparing columns from different sheets » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.