How to Compare Two Columns in Google Sheets

The human eye is not very good at detecting minute differences or similarities between data objects when dealing with vast amounts of data. Fortunately, data processing tools like Google Sheets can notice details that even the most skilled human eye could overlook.

Google Sheets can automate the procedure and produce results almost immediately with the appropriate formulas. Comparing columns, identifying discrepancies, and highlighting commonalities are all tasks that may be completed quickly, accurately, and easily.

In this article, we will demonstrate numerous practical techniques to compare two columns in Google Sheets.

How to Compare Two Columns for Exact Row Matches

Finding exact row matches is the most straightforward method of comparing two columns. This only requires a comparison row by row. Simply look up which rows share the same values and which have different values in Google Sheets, then display the results in a third blank column.

Simply create a new column for the results, and it will show a TRUE if two items in the same row match; otherwise, it will show a FALSE.

For a comparison like this, the formula you would employ is:

This formula determines whether the information in rows 2 of both columns matches. You will receive a TRUE result if they do. If not, the outcome is FALSE.

The steps are as follows:

1. In the second row of column C (in cell C2), insert the formula: =A2=B2
2. Press the enter key.
3. Double click the fill handle to copy the same formula to the rest of the cells in column C.

The outcomes of each comparison should now be shown in column C.

Compare Two Columns and Show Meaningful Text From Numerical Values

You may use an IF statement on numerical data, just like in the example above. As a logical expression, you could use whatever operator or formula you choose to display results for. You may, for instance, use the following formula:

This formula was used in the example below to determine whether Pizza Shop A was more affordable than Pizza Shop B.

Compare Two Columns and Highlight Matching Rows

You can format cells based on a condition using the fantastic technique of conditional formatting. You can use conditional formatting if you prefer to have rows with matching data highlighted rather than results displayed in a separate column.

Here’s how to use conditional formatting in Google Sheets to highlight matching rows:

1. Click the Format menu from the menu bar.

1. Select the ‘Conditional Formatting’ option.

1. By doing so, the sidebar labeled “Conditional format rules” will open to the right of the window.

1. Enter the range of cells you want to compare in the input box next to “Apply to range.” For our instance, we can enter A2:B9.

1. Then click on the dropdown arrow next to “Format cells if” in the Format rules section.

1. Select “Custom formula is” from the dropdown menu that appears.

1. A text box will be visible underneath the drop-down menu. There, enter the custom formula “=$A2=$B2”.

1. Select “Formatting style” and then select “Fill Color.”

1. Pick the color you want to use to highlight rows and cells that match.

1. Finally, to finish up Conditional formatting, click the Done button.

Now, all rows and cells that match should be highlighted in the color

Finding Corresponding Data by Comparing Two Columns in Google Sheets Conditional Formatting

The methods we have explored thus far essentially determined if cells in the same row were matching. What happens, though, if you want to compare two columns and determine which column A values, regardless of which row they appear in, are recurring in column B?

Here’s how to highlight duplicate data using conditional formatting:

1. Click the Format menu from the menu bar.
2. Select the ‘Conditional Formatting’ option.

1. By doing so, the sidebar labeled “Conditional format rules” will open to the right of the window.

1. Enter the range of cells to which you want to apply the formatting in the input box next to “Apply to range.” We just want to see the highlights of column B in our example. Thus, we enter B2:B9.

1. Then click on the dropdown arrow next to “Format cells if” in the Format rules section.
2. Select “Custom formula is” from the selection option that opens.
3. You will see an input box below the dropdown list. Type your custom formula there: =COUNTIF($A$2:$A$9,B2)>0

1. Select “Formatting style” and then select “Fill Color.”
2. Choose the color you want to use to highlight related cells or rows.
3. Finally, let Conditional formatting finish its job by clicking the Done button.

How to Compare Two Columns in Google Sheets – Using VLOOKUP Function

In this section, we’ll look at how to use the VLOOKUP function in Google Sheets to compare two columns. VLOOKUP is a sophisticated function to accomplish the same task and VLOOKUP does not return the row number or the Boolean volume; instead, it returns a “not found” error when the value of the search key cannot be found in both columns.

1. Write the VLOOKUP function

1. Enter the search criteria for the cell number.

1. Pass the area you want to search for your key in ($ Notation).

1. Pass the index as 1

1. Pass the true if you want to retrieve just precise matches, and the false if you want partial matches.

If the value is present in both columns, the VLOOKUP function will return the same result; otherwise, it will return an error.

Comparing Columns In Google Sheets

As you can see, there are a ton of different ways to use Google Sheets. You can adapt these methods to make highly potent data comparisons depending on what you need Google Sheets to do. The applications can be as straightforward as organizing an inventory of your record collection or as complex as examining a company’s product line.

To make your spreadsheet more organized, check out FileDrop. FileDrop is a sidebar app for Google Sheets and Docs. With it you can insert any file type you need in your Google Sheets cells. Start your trial today!