How to compare two columns in Excel and find matches? As professionals who work with data, we know the value of keeping our data organized and accurate. When working with large data in Excel, it can be challenging to write data as correct and it matches up. Fortunately, Excel provides a feature for it. And it can help us compare and match columns quickly and accurately.
Table of Contents
Getting Started: excel compare two columns
Before we look at details of compare two columns in excel for matches, let’s take a moment to review some basic concepts. When we talk about matching columns in Excel, we compare two or more columns to find the matching or non-matching values.
compare 2 columns in excel
For example, let’s say we have two columns. And one containing a list of names and the other containing a list of phone numbers. We want to match the names in column A with the phone numbers in column B. So we can ensure that all the phone numbers are correct and belong to correct person.
To match columns in Excel, we use the IF function. The IF function is one of the most common functions in Excel. And generally, people use it to test a condition and return one value. These are if the condition is true and another value if the condition is false.
Simple Column Matching and comparing two columns of data in excel
Ok now we think about simple column matching scenario. We have two columns. These are A and B. And we want to compare them to find the matching values. Here’s how we can do it with Excel.
- In cell C1, you will enter the following formula: =IF(A1=B1,A1,””)
- Now, you will copy the formula down to the rest of the cells in column C.
The formula in cell C1 compares the values in cells A1 and B1. If the values match, the formula returns the value in cell A1. Otherwise, it returns an empty string. We copy the formula to the rest of the cells in column C. So that, we can compare all the values in columns A and B.
Conditional Column Matching: compare two columns in excel for matches
Now let’s look at a more complex scenario. Here we want to match columns based on a condition. For example, we have two columns. Again these are A and B. And we want to match the values in column A with those in column B. So, if the values in column A contain a certain string.
how can i compare two columns in excel
Here’s how we can do it with comparing formula using IF.
- In cell C1, you should write down our formula: =IF(ISNUMBER(SEARCH(“string”,A1)),B1,””)
- Now again, you will copy the formula down to the rest of the cells in column C.
The formula in cell C1 checks if the value in cell A1 contains the string “string” using the SEARCH function. If the value contains the string, the formula returns the value in cell B1. If not then, it returns an empty string. Here again, we will copy the formula to the rest of the cells in column C to compare all the values. They will be compared to values in columns A and B based on the condition.
Matching Columns in Different Worksheets
Sometimes we must match columns in different worksheets in the same Excel file. And we can compare 2 columns in excel combining with Vlookup function.
- In cell C1 of the first worksheet, you should write this formula: =VLOOKUP(A1,'[worksheet name]Sheet2′!A:B,2,FALSE)
- You will enlarge the formula down to the rest of the cells in column C.
The formula in cell C1 uses the VLOOKUP function to search for the value in cell A1. And it is in the first column of the second worksheet.
Compare 4 Columns In Excel and Find Matches
If you go big and need to compare 4 columns in Excel you can use the following steps with if formula.
- First of all, you will select the first empty cell in a new column (e.g., cell E1). here we will see the results.
- Our new formula is below:
=IF(AND(A1=B1,B1=C1,C1=D1),A1,””)
- Now, you only need to press enter to confirm the formula.
- Then, you can copy the formula down to the rest of the cells by dragging the fill handle (the small square in the bottom right corner of the cell) down to the last row.
- The results will show in the column E. Hence, this is indicating a match between the data in columns A, B, C and D. If there is a match, the formula will return the value in column A. If not then, it will display an empty cell.
Note: If your data starts from a different row, you must adjust the formula’s cell references accordingly. For example, if your data starts from row 2, you would need to use the formula =IF(AND(A2=B2,B2=C2,C2=D2),A2,””) in cell E2.
How to Compare Two Columns In Excel and Count Matches
If you need to compare two columns in Excel and count matches, you can use below steps:
- Firstly, you should select an empty cell to show matches (e.g., cell C1).
- You can use the below formula.
=SUMPRODUCT(–(A1:A10=B1:B10))
- And then, you will replace A1:A10 with the range of cells in the first column you want to compare. And then, “B1:B10” with cells in the second column. You must ensure the ranges have the same number of cells and are in the same order.
- After hitting enter, the results will show in the cell C1.
Note: If your data starts from a different row, you must adjust the formula’s cell references accordingly. For example, if your data starts from row 2, you must use the formula =SUMPRODUCT(–(A2:A11=B2:B11)) in cell C1. Also, use the double negative “–” before the comparison operator to convert the TRUE and FALSE values to 1s and 0s. So that SUMPRODUCT function can sum.
excel calculate difference between 2 cells
In conclusion, comparing and matching columns in Excel is a need for everyone when working with large data. But after this article, you can quickly identify matches between columns.
Whether you need to use excel compare two columns, these formulas make it easier and more efficient.
Hello, I’m Cansu, a professional dedicated to creating Excel tutorials, specifically catering to the needs of B2B professionals. With a passion for data analysis and a deep understanding of Microsoft Excel, I have built a reputation for providing comprehensive and user-friendly tutorials that empower businesses to harness the full potential of this powerful software.
I have always been fascinated by the intricate world of numbers and the ability of Excel to transform raw data into meaningful insights. Throughout my career, I have honed my data manipulation, visualization, and automation skills, enabling me to streamline complex processes and drive efficiency in various industries.
As a B2B specialist, I recognize the unique challenges that professionals face when managing and analyzing large volumes of data. With this understanding, I create tutorials tailored to businesses’ specific needs, offering practical solutions to enhance productivity, improve decision-making, and optimize workflows.
My tutorials cover various topics, including advanced formulas and functions, data modeling, pivot tables, macros, and data visualization techniques. I strive to explain complex concepts in a clear and accessible manner, ensuring that even those with limited Excel experience can grasp the concepts and apply them effectively in their work.
In addition to my tutorial work, I actively engage with the Excel community through workshops, webinars, and online forums. I believe in the power of knowledge sharing and collaborative learning, and I am committed to helping professionals unlock their full potential by mastering Excel.
With a strong track record of success and a growing community of satisfied learners, I continue to expand my repertoire of Excel tutorials, keeping up with the latest advancements and features in the software. I aim to empower businesses with the skills and tools they need to thrive in today’s data-driven world.
Suppose you are a B2B professional looking to enhance your Excel skills or a business seeking to improve data management practices. In that case, I invite you to join me on this journey of exploration and mastery. Let’s unlock the true potential of Excel together!
https://www.linkedin.com/in/cansuaydinim/