How to Compare Two Columns in Excel: A Comprehensive Guide

Compare And Match Columns In Excel: A Comprehensive Guide

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.



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.

How To Compare Two Columns In Excel
Compare And Match Columns In Excel

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.

  1. In cell C1, you will enter the following formula: =IF(A1=B1,A1,””)
  2. 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.

  1. In cell C1, you should write down our formula: =IF(ISNUMBER(SEARCH(“string”,A1)),B1,””)
  2. 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.

  1. In cell C1 of the first worksheet, you should write this formula: =VLOOKUP(A1,'[worksheet name]Sheet2′!A:B,2,FALSE)
  2. 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.

  1. First of all, you will select the first empty cell in a new column (e.g., cell E1). here we will see the results.
  2. Our new formula is below:

=IF(AND(A1=B1,B1=C1,C1=D1),A1,””)

  1. Now, you only need to press enter to confirm the formula.
  2. 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.
  3. 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:

  1. Firstly, you should select an empty cell to show matches (e.g., cell C1).
  2. You can use the below formula.

=SUMPRODUCT(–(A1:A10=B1:B10))

  1. 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.
  2. 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.

Related posts


Leave a Comment