Many times, we need to compare two columns in excel. For example, we may want to know which product is selling better among the two. But how to compare two columns in excel?

This post shows you how to achieve this objective. The MS Excel function for comparing two columns is usually performed in different ways.

It can be used to compare up to nine columns or ranges of cells, but it can also be used to compare three or four columns.

The MS Excel option for comparing two columns is an easy task and can perform in a few minutes.

Compare Two Columns (Side by Side)

This is the most basic type of comparison, and you can easily check whether a cell value in one column is equal to or unequal to another by checking if the corresponding values from another column match or don’t match up.

When you compare two numbers across a single row it’s referred to as cross-tabulation, but when you compare combinations within single columns it’s called an inner-tabulation.

For smaller datasets, the Excel formula and / OR manual comparison method works just fine. When you need to perform this type of analysis on larger data sets.

We recommend using a custom-built script in all programming languages ​​such as VBA, Python, or R – it can provide significant performance speed.

Compare Side by Side Using the Equal to Sign Operator

Suppose you have the following dataset and you want to know which rows contain matching data.

If you want to compare two columns (side by side) in a spreadsheet, then simply use the formula that follows: =A2=B2

The above formula will always return the same value, either TRUE or FALSE (based on both values) unless one of the values is FALSE.

Then you can use a procedure known as filtering to get TRUE for only the TRUE values and to get FALSE for all other values.

For example, let’s say our formula returns TRUE for 1, 2, and 5 (only these three numbers). To only get the values 1 and 2, we filter out 4 and 7 because they are not TRUE.

Compare Side by Side Using the IF Function

If you want to find out if there is a match in one column with another, you can do that using the IF statement! The IF statement will give two options; look for matches or look for differences.

For example, if any of the values in your first column matches any of the values in your second column, it’ll display “Match”.

Otherwise, if all values in one column don’t match any specific value in the other column, it will display “Mismatch”.

The IF function is quite helpful for any formula that requires a conditional statement. Specifically, the IF function calculates an outcome depending on whether or not the first parameter equals the second parameter.

For example, if cell A2 equals cell B2, then “Match” displays in the cell, otherwise “Not a Match” displays (assuming cell B2 is blank).

The formula below will perform the calculation when the cell values match otherwise it will return blank values. =IF(A2=B2,”Match”,”Not a Match”).

Compare Two Columns Using VLOOKUP

In the above examples, I showed you how to find data in a single column. You can generally expect to be working with two columns of data rather than just one so it’s important to know how this affects things.

Let me show you two other examples where we use VLOOKUP but this time to see what items exist in one column only when they are combined with another column – or rather, if there is no match at all.

Suppose we have a dataset in Excel with names in column A and a grade in column B. If you want to find out who is in the class that’s also in the database, use this formula:

=IFERROR(VLOOKUP(B2,$A$2:$A$10,1,0), “No Match”). This formula compares what you have in Column B with what is in your database from “A” to “E” and returns the name when it appears not only in Column A but also when it appears in Column B.

The VLOOKUP function by default will return a #N/A error for an invalid key. To avoid getting the #N/A error, wrap this around an IFERROR function that will otherwise return either the word “No Match” if the name isn’t available in Column A or else return “Found” if it does exist.

If one wanted to be even more thorough, they could also wrap this same bit of code around an IF statement.

FAQs

How do I compare two lists in Excel?

Select your cells. Then, select Conditional Formatting and then move to Highlight Cells Rules and then click on Duplicate Values.

Let the cursor hover over the newly created rule for a few seconds until you see the tooltip telling you about which cell it will highlight if there are duplicates.

How do you compare two columns in Excel to find differences?

Select the first cell you want to compare. The first will be at the top of your screen, left to right. From there, place your cursor in each cell until you have an option for changing them all.

You can either change them manually or use Go to Special. On this menu, select Row differences and press OK.

Conclusion

If you have ever had the need to find out which of two columns has more data, then you will know how useful it is to have this information.

Knowing which one has more data means that you can use that column in your formulas. So, in this blog post, we will show you how to compare two columns in excel.

As you can see, there are several ways to compare two columns in Excel. We hope we were able to answer the question on how to compare two columns in Microsoft Excel and that you can use some of the options we discussed to help you in your next project. Thanks for reading and we look forward to your comments.

If you have any questions regarding this visit our website.