Comparing two columns in excel spreedsheet

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.