How to Compare Two Columns in Google Sheets (2024 Update)

Compare Two Columns in Google Sheets for Matches

You can use conditional formatting to visually compare two columns and highlight cells in one column that match the cells in another column.

Step 1: Select the Range for Formatting

Select the range you want to format. Here, we have our data set from A1:A5.

Step 2: Access Conditional Formatting Options

Go to Format > Conditional formatting.

Step 3: Set Up a Custom Formula

Under the "Format cells if" drop-down menu, choose "Custom formula is". To highlight cells in Column A that match any cell in Column B, you can use the following formula: =COUNTIF(B:B, A1)>0

Step 4: Apply a Formatting Style of Your Choice

Choose your formatting style and click "Done".

Here's how your data should look like:

Compare Two Columns In Google Sheets for Differences

To find differences between two columns, you can follow the same steps above for conditional formatting. However, this time, the custom formula would be: =COUNTIF(B:B, A1)=0

Here's what our example looks like: 

Compare Two Columns in Google Sheets to Find Missing Values Using MATCH and ISNA Functions

To compare two columns in Google Sheets and find missing values, you can use a combination of built-in functions.

Suppose Column A and Column B are the two columns you want to compare, with Column A being the reference.

Step 1: Enter Formula for Non-Matching Values

In an empty cell in Column C (e.g., C1), enter the formula: =ISNA(MATCH(A1, B:B, 0)).

Step 2: Extend Formula to Additional Cells

Drag the fill handle (a small square at the bottom right corner of the cell) down Column C to apply this formula to other cells corresponding to the entries in Column A.

Step 3: Identify Non-Matching and Matching Values

This formula will return TRUE for cells in Column A that don't have a matching value in Column B, and FALSE for those that do.

Compare Two Columns in Google Sheets to Find Missing Values Using FILTER and ISNA with MATCH

Follow the steps below if you prefer to get a list of missing values directly:

Step 1: Enter the Formula

In an empty cell (e.g., C1), enter the formula: =FILTER(A:A, ISNA(MATCH(A:A, B:B, 0))).

Step 2: Press Enter

Google Sheets will now display all values in Column A that do not have a match in Column B.

This combines the FILTER function with the MATCH and ISNA functions to directly filter and display all missing values from Column A.

Compare Two Columns in Google Sheets for Duplicates

To mark duplicate values in Column A that are found in Column B, you can use the following formula in a new column (say, C): 

=IF(COUNTIF(B:B, A1)>0, "Duplicate in B", "")

We hope that this article has helped you and given you a better understanding of how to compare two columns in Google Sheets. If you enjoyed this article, you might also like our articles on how to do string interpolation in Google Sheets and how to format ZIP codes in Google Sheets.