Excel Compare Columns

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.

Excel compare columns with VLOOKUP

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 –

Excel compare columns with an IF statement

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.

Excel compare columns with subtraction

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

Join Our Free Newsletter

Featured guides and deals

You may opt out at any time. Read our Privacy Policy

Leave a Comment