How to Compare Different Columns in a Spreadsheet in Excel Formula

Comparing different columns in a spreadsheet is a common task in Excel. You may want to compare two or more columns to find matches, differences, missing values, or duplicates. There are various methods to do this in Excel, depending on your data structure and what you want to achieve. In this article, we will show you some of the most useful methods to compare different columns in a spreadsheet in Excel formula.

To compare different columns in a spreadsheet in Excel formula, you need to use logical operators, functions, or conditional formatting. Logical operators, such as equal to (=), not equal to (<>), greater than (>), less than (<), and so on, allow you to compare two values and return TRUE or FALSE. Functions, such as IF, VLOOKUP, MATCH, COUNTIF, and so on, allow you to perform calculations or lookups based on the comparison results. Conditional formatting allows you to apply different formats, such as colors, icons, or data bars, to highlight the comparison results visually.

The procedures to compare different columns in a spreadsheet in Excel formula depend on the specific method you choose. Here are some of the most common methods and their steps:

Method 1: Compare Two Columns for Exact Row Match

This method allows you to compare two columns row by row and check whether they have the same or different values. You can use a simple formula with the equal to (=) operator or the IF function to do this.

• Select a cell in the same row as the columns you want to compare.
• Enter the formula =A2=B2 or =IF(A2=B2,"Match","Mismatch"), where A2 and B2 are the cells you want to compare. You can change the text “Match” and “Mismatch” to any values you want.
• Copy the formula down to other cells by dragging the fill handle (a small square in the bottom-right corner of the selected cell).
• You will get TRUE or FALSE, or “Match” or “Mismatch”, depending on the formula you used.

Method 2: Compare Two Columns and Highlight Matches or Differences

This method allows you to compare two columns and highlight the cells that have matching or different values. You can use conditional formatting to do this.

• Select the columns you want to compare (don’t include the headers).
• Click the Home tab and then click Conditional Formatting.
• Select New Rule from the drop-down menu.
• From Select a Rule Type, click Use a formula to determine which cells to format.
• Enter the formula =$A2=$B2 into the field below Format values where this formula is true, where A2 and B2 are the cells you want to compare. You can change the operator (=) to any other logical operator you want.
• Click the Format button and choose the format you want to apply, such as fill color, font color, or border.
• Click OK to close the Format Cells dialog box and then click OK again to close the New Formatting Rule dialog box.
• You will see the cells that match or differ from the comparison criteria are highlighted with the format you chose.

Method 3: Compare Two Columns and Find Missing Values

This method allows you to compare two columns and find the values that are in one column but not in the other. You can use the VLOOKUP function or the MATCH function to do this.

• Select a cell in the same row as the columns you want to compare.
• Enter the formula =ISNA(VLOOKUP(A2,B:B,1,FALSE)) or =ISNA(MATCH(A2,B:B,0)), where A2 is the cell you want to look for and B:B is the column you want to look in. You can swap the columns if you want to do the reverse lookup.
• Copy the formula down to other cells by dragging the fill handle.
• You will get TRUE or FALSE, depending on whether the value is found or not.

Explanation

To compare different columns in a spreadsheet in Excel formula, you need to understand how logical operators, functions, and conditional formatting work. Here is a comprehensive explanation of each method we discussed above:

Method 1: Compare Two Columns for Exact Row Match

The equal to (=) operator compares two values and returns TRUE if they are equal and FALSE if they are not. For example, the formula =A2=B2 will return TRUE if the value in cell A2 is the same as the value in cell B2, and FALSE otherwise. You can use this formula to compare two columns row by row and get a simple result of TRUE or FALSE.

The IF function allows you to perform a logical test and return one value if the test is true and another value if the test is false. For example, the formula =IF(A2=B2,"Match","Mismatch") will return “Match” if the value in cell A2 is the same as the value in cell B2, and “Mismatch” otherwise. You can use this formula to compare two columns row by row and get a more descriptive result of “Match” or “Mismatch”.

Method 2: Compare Two Columns and Highlight Matches or Differences

Conditional formatting allows you to apply different formats to cells that meet certain criteria. You can use a formula to determine which cells to format based on the comparison results. For example, the formula =$A2=$B2 will return TRUE if the value in cell A2 is the same as the value in cell B2, and FALSE otherwise. You can use this formula to highlight the cells that have matching values with a certain color. You can also change the operator (=) to any other logical operator, such as not equal to (<>), greater than (>), less than (<), and so on, to highlight the cells that have different values.

Note that you need to use absolute references ($sign)forthecolumnlettersandrelativereferences(no$ sign) for the row numbers when entering the formula in conditional formatting. This ensures that the formula is applied to each cell in the selected range correctly.

Method 3: Compare Two Columns and Find Missing Values

The VLOOKUP function allows you to look for a value in the first column of a table and return a value from another column in the same row. For example, the formula =VLOOKUP(A2,B:B,1,FALSE) will look for the value in cell A2 in the first column of the table B:B and return the value from the same column (1) in the same row. The last argument (FALSE) means that the lookup is exact and not approximate. You can use this formula to compare two columns and find the values that are in one column but not in the other.

The MATCH function allows you to look for a value in a range and return its relative position. For example, the formula =MATCH(A2,B:B,0) will look for the value in cell A2 in the range B:B and return its position in the range. The last argument (0) means that the match is exact and not approximate. You can use this formula to compare two columns and find the values that are in one column but not in the other.

The ISNA function allows you to check whether a value is the error value #N/A, which means not available. For example, the formula =ISNA(VLOOKUP(A2,B:B,1,FALSE)) will return TRUE if the VLOOKUP function returns #N/A, which means the value in cell A2 is not found in the table B:B, and FALSE otherwise. You can use this formula to compare two columns and find the missing values.

Example

To illustrate how to compare different columns in a spreadsheet in Excel formula, let’s use a simple scenario and example. Suppose you have two lists of products in columns A and B, and you want to compare them for the following purposes:

• Find out which products are in both lists (matches).
• Find out which products are in one list but not in the other (differences).
• Find out which products are missing from either list (missing values).

Here is how the data looks like:

Here is how you can use the methods we discussed above to compare the two columns:

Method 1: Compare Two Columns for Exact Row Match

To compare the two columns for exact row match, you can use the formula =IF(A2=B2,"Match","Mismatch") in cell C2 and copy it down to other cells. You will get the following result:

As you can see, none of the products in the same row match, so you get “Mismatch” for all the rows.

Method 2: Compare Two Columns and Highlight Matches or Differences

To compare the two columns and highlight the matches or differences, you can use conditional formatting with the formula =$A2=$B2 and apply a green fill color to the cells that match. You will see the following result:

As you can see, none of the cells are highlighted with green color, which means none of the products in the two columns match.

Alternatively, you can use conditional formatting with the formula =$A2<>$B2 and apply a red fill color to the cells that differ. You will see the following result:

As you can see, all of the cells are highlighted with red color, which means all of the products in the two columns differ.

Method 3: Compare Two Columns and Find Missing Values

To compare the two columns and find the missing values, you can use the formula =ISNA(VLOOKUP(A2,B:B,1,FALSE)) in cell C2 and copy it down to other cells. You will get the following result:

As you can see, the cells that have TRUE in column C indicate that the product in column A is not found in column B. For example, Apple and Eggplant are missing from column B.

Similarly, you can use the formula =ISNA(MATCH(B2,A:A,0)) in cell D2 and copy it down to other cells. You will get the following result:

As you can see, the cells that have TRUE in column D indicate that the product in column B is not found in column A. For example, Durian and Honeydew are missing from column A.