Excel Compare Two Columns for Matches (Table of Contents)
Introduction to Compare Two Columns in Excel
Excel is the most potent tool on the planet and we all will agree with the fact. We can do almost everything using Excel when it comes to data analysis. It is not only a tool that allows you to store the data but more than that. When it comes to comparing and matching different data sets or for that sake, different columns, we have several options to do. Comparing two or more columns in Excel for Matches is also a task for every data analyst and in this article, we are going to see different methods using which we can compare two columns in Excel for matches.
What is Comparing Two Columns in Excel for Matches?
When we are doing data analysis in Excel, one of the most important tasks while doing so is comparing two columns and figure out whether the values within them are matching row by row. This has importance based on the situation. Sometimes, you just want to truncate your dataset, and while doing that it is of your core interest to reduce the duplication. You may want to check if the values in two columns are matching. If it does, you may opt to delete one of the two columns. Or, you may be having some comparison and trying to see if that matches between the two columns from two different datasets for with same properties. This way or that, you may need some methods to compare two columns for matches. We will walk you through some of the methods to achieve this.
Methods to Compare Two Columns for Matches
We will discuss some methods to compare two columns for matches.
Method #1
Consider data as shown in the screenshot below which includes different text values.
To check whether two of the columns are matching or not, we can use the simplest method. Follow the steps below:
Step 1: In cell C2, start initiating the formula using equals to sign (“=”). You can use your keyboard button for the same.
4.9 (2,356 ratings)
View Course
Step 2: Since we wanted to compare the value present in cell A2, use A2 as the first argument under C2 after the equals to sign.
Step 3: Now, we wanted to check if the value present in A2 is matching with the value present in B2, for that, use =B2 as a next argument. The entire formula will read as =A2=B2.
This is a logical formula that checks whether the value present in cell A2 is exactly matching with the value present in cell B2. If both values match, then it will return Boolean output TRUE. If both values don’t match, it will return Boolean output FALSE.
Now, drag the formula across all the working cells and see the output as shown below:
- You can see some of the cells will match with each other from both of the columns and some of those will not. This is because for output in C2, A2 has value “Sweets” whereas value in B2 is “swetes”. Same as with the 4th output where A4 has value as “Google” and B4 has “Googl”. Etc.
- Note that, this criterion for matching does not consider whether the text is in the upper or lower case.
- There are sometimes the extra spaces that allow the text to not match using the equality operator. For Example, see the fifth row where two strings are looking exactly similar and they should match with each other. But the result is FALSE because we have an extra spacing at the end of the cell B5. You can validate this with using Len function as well.
This is one method using which we could check whether the values under two columns are having a match or not. Let’s see a different method to check the same.
Method #2
Now, we are going to see a different method where we are going to use a conditional IF statement to check whether two of the columns have matches or not. Follow the steps below.
Sometimes, juts getting values as TRUE/FALSE might not be a good representative when we are comparing two columns for matches. Having a proper phrase that can let you know whether the data is matching or not matching will be helpful in such cases. Let’s create a custom formula using a conditional IF statement that works in our favor.
Step 1: Consider the same example as the previous one. In cell C2, initiate a formula using a conditional IF statement as shown below:
Step 2: The first argument under conditional IF needs a logical test. In our example, we need to check whether two of the columns have matching values or not, therefore, we can use A2=B2 as a logical test under the IF statement. See the screenshot below:
You need to use a comma as a separator to separate multiple arguments of the IF statement.
Step 3: Now, we need to specify value for the argument [value_if_true]. This should be the value that will reflect in cell C2 if the logical test has output as TRUE. We will use the keyword “Matching” as a value for this argument.
Step 4: On the similar lines, we must specify a value for the argument [value_if_false]. This will be the value being reflected in cell C2 if the logical condition has output as FALSE. We will use “Not Matching” as a value for this argument.
Don’t forget to use the double quotes within which the values should be added. Otherwise, there would be an error with the formula.
Step 5: Use closing parentheses to complete the formula and press Enter button to see the output.
You can see the value we are getting under C2 is “Not Matching”. Because the text in two columns is not matching with each other.
Step 6: Drag the formula across the rows to see whether two columns are having matching values with each other or not.
These are the two methods using which we can compare two columns in Excel for matches. We will end this article here and have some points to be remembered for you guys on the same.
Things to Remember
- You can use the equality operators the same way we use it to compare two values in general mathematics. If the values do match, it will return TRUE as output and if values do not match, it will return FALSE.
- The equality operator doesn’t count the case of the text. Ex. DUBAI, Dubai, dubai are all the same for this operator.
- We can also customize the results by simply using a conditional IF statement using which we can get a decent message to pop-up for matches and non-matches instead of TRUE and FALSE.
- On similar lines, we can compare data that has either numeric values or Mixed values in it from two different columns for matches.
Recommended Articles
This has been a guide to Compare Two Columns in Excel for Matches. Here we discuss How to Compare Two Columns in Excel for Matches along with practical methods and downloadable excel template. You can also go through our other suggested articles –
