Comparing two columns in excel spreedsheet

by Frederick Tybalt on Sep.22, 2008, under Office

Hello there! If you are new here, you might want to subscribe to the RSS feed for updates on this topic.

If you in a software service based industry you may get many questions in Microsoft Excel. One such question is “How to compare two columns in the excel if the data is in thousands of rows”. Here is a solution for it.

Imagine you have two columns A and B. Column A has the data which needs to be compared against column B. Here I would show you 3 excel formulas to archive this.

MATCH
COUNTIF
VLOOKUP

Here is the syntax

=Match( value, array, match_type )
=CountIf( range, criteria )
=VLookup( value, table_array, index_number, not_exact_match )

Here is a example usage :

The below syntax can be used if we want to find out a match and mark the result as ‘Y’ or ‘N’ and consolidate it.
```
=IF(ISNA(MATCH(A1,$B$1:$B$100,FALSE)),"N","Y")
```
The below syntax is used to find the count of the matches in the resultant column. If the count is 0, then there is no match. If the count is 1 then there is 1 match. If the count is 2 then the matched data count is 2 and so on.
```
COUNTIF($B$1:$B$100,A1)
```
The usage below is similar to the first option where there is change in the function idiom
```
IF(ISNA(VLOOKUP(A1,$B$1:$B$100,1,FALSE)),"N","Y")
```

Where to use what?

Now the question is, of all of them which performs the same function and now where to use what function. The MATCH/VLOOKUP can be used to find out the existence of the data in the column but COUNTIF can be used to find out the existence of data and to find the number of instances the data exists.

Which one is faster?

MATCH/VLOOKUP is more faster than the COUNTIF, since COUNTIF has to look up the entire array to throw out the count. But MATCH/VLOOKUP terminates it loop once it find the data in the array.

There are more functions too in excel for comparison of data. I shall explain it when I come across it.

:compare, excel, microsoft, Office

4 Comments for this entry

Summer
May 22nd, 2010 on 2:53 am
Hi

I want to compare two columns that have similar data e.g. one column has the word Vapor and other has Vapour…The result should give me these columns. Is there a way in excel to accomplish this task?

Thanks!

Reply
RJ
March 4th, 2011 on 2:26 am
I am trying to match columns and come up with a result. Here is a sample

Spreadsheet has data in columns A through R

I am focusing on column J (rows 2 thru 17)
column Q (row 2 thru 953)
Column R (row 2 thru 953)

What I need to do is Match data J2 against Q2 thru Q953 and get the data in cell R? to be reflected in the column of choice

Then I need the formula to move down and work down in order for column J.

Did that make sense?

Please help.

Thanks

Reply
Lisa
March 19th, 2011 on 4:15 am
Hi Ray,
Try this:

=index($R:$R,match($J2,$Q:$Q,0))

Where “R” is the column that contains the results you want, “J” has the shorter list of items to which the results can be matched, and “Q” is where Excel will find the match to what’s in column “J”.

Cheers.

Reply
prasanth
July 29th, 2011 on 2:08 am
HI this holds good only if the number of columns are less than 100

Reply