There are plenty of “Excel compare columns” methods that you could employ within Microsoft Excel, and the option that you choose is ultimately going to depend upon what you are attempting to accomplish.
If your “Excel compare columns” search is being triggered in an effort to check one column for instances of a value in another column, then you may want to utilize the VLOOKUP function in a new column within your spreadsheet.
Another way to perform this function is to utilize an IF statement within an Excel formula, which also allows you to check for a specific value within an entire Excel column.
Conversely, if you simply want to look for the difference between values in different columns but in the same row (ex – A1, B1, C1 etc.), then you would simply subtract one value from the other in an empty column, which would show the difference between the two values.
If you are working with a large spreadsheet, consider modifying it so that the top row repeats on every page in Excel.
Excel Compare Columns with VLOOKUP
The VLOOKUP function in Excel works with four variables that you use to check the values in one column for similar values in another column. The function looks like –
=VLOOKUP(xxx, yyy, zzz, FALSE)
The different variables are –
xxx = the cell value that you are looking for
yyy = the range of cells in which you want to look for that value
zzz = the column number in which that range of cells is located
FALSE = this will trigger the function to display “#NA” if no match is found. If a match is found, then the matched value will be displayed instead.
If you look at the picture below, you can see all of the data and the formula used to do a simple example of this Excel column comparison.
Use an IF Statement to Compare Excel Columns
The IF statement has a few more options to do an Excel compare columns exercise, but the syntax is slightly more difficult to understand.
The formula looks like –
=IF(COUNTIF(xxx, yyy),zzz,0)
and the different variables are –
xxx = the column of values that you are checking
yyy = the value in the xxx column that you are looking for
zzz = the value to display if a match is found
0 = the value to display if a match is not found
You can check the picture below for an example –
Compare Excel Columns with a Simple Formula
Sometimes, an Excel compare columns activity is as simple as subtracting a value in one column from a value in another column.
Additionally, once you are familiar with this basic concept, it becomes simpler to determine comparative values between cells all throughout your spreadsheet. You can even use this Excel compare columns structure to perform additional arithmetic operations, such as addition, multiplication, and division.
Check the example image below for more information on how you should structure your formula and your data.
As you can see in the image above, a simple subtraction formula looks like “=XX-YY”, where “XX” is the starting value, and “YY” is the value you are subtracting from it. In basic expressions like this, you can substitute “+” “*” and “/” to perform addition, multiplication, and division, respectively.
Another useful Excel formula is called Concatenate. This provides a simple way to combine data that exists in multiple cells.
Continue Reading
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.