Updated June 8, 2023
Excel Compare Two Columns for Matches (Table of Contents)
Compare Two and Match Columns in Excel
In this article, we will learn about Compare Two Columns in Excel for Match. Excel is the most potent tool on the planet, and we all will agree with this 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 comparing and matching different data sets or, for that sake, different columns, we have several options. Comparing two or more columns in Excel for Matches is also a task for every data analyst. In this article, we are going to see different methods using which we can Compare Two Columns in Excel for Match.
What is Comparing Two Columns in Excel for Match?
When doing data analysis in Excel, one of the most important tasks is comparing two columns and figuring out whether their values match 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 the two columns match. If it does, you may delete one of the two columns. Or, you may be comparing and trying to see if that matches the two columns from two different datasets with the 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.
Compare Two Columns for Match (Examples)
We will discuss some methods to compare two columns for matches.
Examples #1
Consider the data shown in the screenshot below, which includes different text values.
We can use the simplest method to check whether two of the columns match. 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.
Step 2: Since we wanted to compare the value present in cell A2, use A2 as the first argument under C2 after the equals sign.
Step 3: Now, we want to check if the value in A2 matches the value in B2; for that, use =B2 as the 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 each other from both columns, and some will not. This is because, for output in C2, A2 has the value “Sweets”, whereas the value in B2 is “sweets”. Same as with the 4th output where A4 has the value “Google” and B4 as “Google”. Etc.
- Note that this criterion for matching does not consider whether the text is in upper or lower case.
- Sometimes extra spaces allow the text not to match using the equality operator. For Example, see the fifth row where two strings look exactly similar and should match each other. But the result is FALSE because we have an extra spacing at the end of cell B5. You can validate this by using the Len function as well.
This is one method to check whether the values under two columns match. Let’s see a different method to check the same.
Examples #2
Now, we will see a different method where we will use a conditional IF statement to check whether two of the columns have matches. Follow the steps below.
Sometimes, just 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 matches or not matching will be helpful in such cases. Let’s create a custom formula that works in our favor using a conditional IF statement.
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 columns have matching values; 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 the value for the argument [value_if_true]. This value will be reflected 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: We must specify a value for the argument [value_if_false] on similar lines. This value will be 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 the two columns does not match.
Step 6: Drag the formula across the rows to see whether the two columns have 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 like we compare two values in general mathematics. If the values match, it will return TRUE as output; if values do not match, it will return FALSE.
- The equality operator doesn’t count the case of the text. Ex. DUBAI and Dubai are all the same for this operator.
- We can customize the results by simply using a conditional IF statement to get a decent message for matches and non-matches instead of TRUE and FALSE.
- We can compare data with numeric or Mixed values from two different columns for matches on similar lines.
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, practical methods, and a downloadable Excel template. You can also go through our other suggested articles –
