## Comparing two columns in excel spreedsheet

by on Sep.22, 2008, under Office

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.

1. MATCH
2. COUNTIF
3. VLOOKUP

Here is the syntax

1. =Match( value, array, match_type )
2. =CountIf( range, criteria )
3. =VLookup( value, table_array, index_number, not_exact_match )

Here is a example usage :

1. 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")`
2. 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)`
3. 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.

### 4 Comments for this entry

• Summer

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!

• RJ

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?

Thanks

• Lisa

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.

• prasanth

HI this holds good only if the number of columns are less than 100

### Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

### Blogroll

A few highly recommended websites...

### Archives

All entries, chronologically...