Excel - Comparing two columns A&B

November 2016

Microsoft Excel is a widely used programme which simplifies all manual calculations using columns. The presence of multiple numerical values when comparing columns can make calculations tedious. This article will highlight and solve the query of comparing the values of two Microsoft Excel sheet columns. Finding values in column A but not in column B can be carried out by matching column A's value with B, assuming the values start from the 2nd column. The result of the below syntax will appear in another column, say column C.


Issue


Please help me to find the values in column A but not in column B.

Column A column B    

5000 4001    
5001 4080    
5002 4445    
5003 4493    
5004 4758    
5005 4967    
5006 5001    
5007 5003    
5008 5004    
5010 5005

Solution


Try this:

Assumptions.
  • 1. Data starts from Row 2
  • 2. You want the results in column C


In cell c2, write

=IF(ISERROR(MATCH(B2,A$2:A$11,0)), "", MATCH(B2,A$2:A$11,0)) 


This will let you know which row of column A has the matching value for the value in column B that is being checked.

To check which row of column B has the matching value for the value in column A that is being checked
=IF(ISERROR(MATCH(B2,A$2:A$11,0)), "", MATCH(B2,A$2:A$11,0))

Note


Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - Comparing two columns A&B » 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.