Comparing columns is an efficient way to remove anomalies and redundancy in a workbook. So, do you want to know how to compare two columns in Excel?
In this article, you will get to know about Excel compare columns. So, let’s get started:
How to Compare Two Columns in Excel?
Method 1: Using a formula.
You can use a conditional statement, and this will allow you to check a particular segment of the sheet rather than checking the whole of the sheet itself. For this, you will have to consider the following steps or points.
Step 1: In this first step of this method, you will have to determine the columns of which you want to compare the data. For ease of comparison, you can consider copying both the columns onto a new sheet as this will make things easy for you.
Step 2: Now, type the formula which is essentially an if statement. For this, you can either click on the second column or a blank cell. After this, all that you require is to type the formula there.
This way you can easily compare the data of two columns using a formula.
Alternatively, you can use VLOOKUP or HLOOKUP, or MATCH function.
Don’t worry we will tell you about each of these in precise detail so that you can make use of it to compare two cells in excel.
The V in VLOOKUP stands for vertical. So, if you want to compare data of two columns, then you should consider using VLOOKUP.
The general syntax goes as “VLOOKUP (lookup_value, table_array, col_index_num, range_lookup).”
So, let’s explain the arguments. Lookup_value is the value that you need to search. Table_array is the corresponding column on which you want to do a search. Col_index_num is the column number.
If you want an exact result, then you will also have to specify the renge_lookup attribute. However, this argument is optional, and you may skip it.
Now, moving onto the HLOOKUP function. The general syntax goes as “HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).”
Now, we need not specify the meanings of the arguments as they have their usual meanings which we have already specified above.
The last function which may come in handy while comparing two columns in Excel is the “Match” function.
What this extremely useful function does is that it locates the mentioned term within the range and reverts with the position of the specified term.
If somehow the value is not found then the result will be displayed as “#N/A.”
Now, let’s state the general structure of the MATCH function. It goes as: “MATCH (lookup_value, lookup_array, [match_type])”. Lookup_value is the value that needed to be searched for.
Lookup_array is the domain range. The last argument which is “Match_type” is an optional argument which specifies that whether you want an exact match or an appropriate result.
To obtain an exact match consider “0” as the argument.
This way you can easily compare 2 columns in Excel.
Method 2: Using side by side.
Just in case if you want to compare data from two different workbooks then you should have a look over this.
Though in this method, you will have to compare the data from the workbooks manually, this method is worth every bit as it gives you a side-by-side view of both workbooks.
So, let’s get started:
Step 1: You need not perform any extraordinary thing as your first step. You simply need to launch both of your workbooks, and that’s it. You have completed your first step, and you can now proceed with the next steps.
Step 2: From the navigation panel, click on the “view” tab. This will get you to the view options. Now, you need to navigate to the “View side by side”. Once you have found it then you now only need to click on it. The “View side by side” option can be found in the Windows section of the “View” tab.
Step 3: Your recent activity of yours will result in a dialog box. On the dialog box, you will be prompted to select the workbook which you wanted to compare with the active one. Once you have selected the desired one, then you will now see both the workbooks in the side-by-side view.
Now, to manually review and compare both workbooks, you only need to scroll down the documents.
The scrolling will be done in sync and this way you can easily spot the differences by manually going through them.
See, comparing two columns in Excel was never this easy.
Method 3: Using a Visual Basic macro.
Did you know that you can use a visual basic editor to compare the data in Excel? If not then this section will do the needful for you. So, here we go with the stuff:
Step 1: If you are using Microsoft Excel 2013 then all that you need to open a visual basic editor is to press “Alt + F11” and this will help you to achieve the needful.
However, if you are using Excel 2010, then you can consider going for the developer’s tab and then selecting “Visual Basic” from there.
Step 2: Now, you need to go this way: Insert -> Module.
Step 3: Now, you need to get the compare range code, and you can easily get it from the Microsoft Knowledge Base article. Once you have got that, then you need to copy it.
Step 4: Select the module sheet and paste the code which you have just copied in step #3.
Step 5: Now, you need to get back to the Excel sheet and for this, you can consider hitting “Alt + F11.” This will take you back to Excel. Make sure to leave column B blank.
Step 6: Now, you are required to highlight the “A1: A5” column and then click on “Mac.”
Step 7: After this, you require selecting “find matches” and then click “Run.” Once you are done with this, then you need to look up the other column for duplicate values.
This is the method by which you can compare two cells in Excel.
So, this was all about how to compare two columns in Excel. Do we hope that this article must have ended your quest on how to compare data in Excel?
Quick Links –
- OptimizePress Review: Is It Worth the your Money? (Great Deal $199/yr)
- How to Reset Wifi Password
- How to Get Rid of Syntpenh .exe Error?
Final Thoughts
If you liked this article, then do tell us through the comments section below.
Thank you for passing by.
