Are you struggling to identify small differences or similarities between large amounts of data? Look no further! Google Sheets is here to save the day. With its data processing capabilities, Sheets can detect details that even the most experienced human eye might overlook.
In this article, we will introduce you to several practical techniques for comparing two columns in Google Sheets – techniques that are quick, accurate, and effortless.
Comparing Two Columns to Find Exact Matches
If you’re looking for exact matches between rows, this is the simplest method to compare two columns. It only requires a line-by-line comparison. All you need to do is search for rows that share the same values and those with different values in Google Sheets, then display the results in a blank third column.
Simply create a new column for the results, which will display TRUE if two elements in the same row match, otherwise it will display FALSE.
To perform this type of comparison, use the following formula:
=A2=B2This formula determines if the information in row 2 of both columns matches. You will get a result of TRUE if it does. Otherwise, the result will be FALSE.
Here are the steps to follow:
- In the second row of column C (cell C2), insert the formula:
=A2=B2 - Press Enter.
- Double-click the fill handle to copy the same formula to the rest of the cells in column C.
The results of each comparison should now be displayed in column C.
Comparing Two Columns and Displaying Meaningful Text from Numeric Values
You can also use an IF statement on numeric data, just like in the previous example. As a logical expression, you can use any operator or formula of your choice to display the results. For example, you can use the following formula:
=IF(A2>B2,"Pizza Shop A is more affordable than Pizza Shop B","Pizza Shop B is more affordable than Pizza Shop A")This formula was used in the example below to determine if Pizza Shop A was more affordable than Pizza Shop B.
Highlighting Corresponding Rows When Comparing Two Columns
If you prefer to highlight rows with corresponding data rather than displaying the results in a separate column, you can format cells based on a condition using the fantastic technique called conditional formatting. Here’s how you can use conditional formatting in Google Sheets to highlight corresponding rows:
- Click on the “Format” menu in the menu bar.
- Select “Conditional formatting.”
- This will open the “Conditional formatting rules” sidebar to the right of the window.
- Enter the range of cells you want to compare in the input box next to “Apply to range.” In our example, we can enter A2:B9.
- Next, click on the dropdown arrow next to “Format cells if” in the conditional formatting rules section.
- Select “Custom formula is” from the dropdown menu that appears.
- A text box will appear below the dropdown menu. Enter the custom formula “$A2=$B2”.
- Select “Formatting style” and then select “Fill color.”
- Choose the color you want to use to highlight the rows and cells that match.
- Finally, to finish the conditional formatting, click the “Done” button.
Now, all the rows and cells that match should be highlighted with the chosen color.
Finding Matching Data by Comparing Two Columns in Google Sheets with Conditional Formatting
The methods we have explored so far essentially determine if the cells in the same row match. But what if you want to compare two columns and determine which values from column A, regardless of the row they appear in, repeat in column B? Here’s how you can highlight duplicate data using conditional formatting:
- Click on the “Format” menu in the menu bar.
- Select “Conditional formatting.”
- This will open the “Conditional formatting rules” sidebar to the right of the window.
- Enter the range of cells that you want to apply formatting to in the input box next to “Apply to range.” We only want to see the highlights in column B in our example, so we enter B2:B9.
- Next, click on the dropdown arrow next to “Format cells if” in the conditional formatting rules section.
- Select “Custom formula is” from the dropdown option that opens.
- You will see an input box below the dropdown menu. Type in your custom formula:
=COUNTIF($A$2:$A$9,B2)>0 - Select “Formatting style” and then select “Fill color.”
- Choose the color you want to use to highlight the matching cells or rows.
- Finally, to finish the conditional formatting, click the “Done” button.
Comparing Two Columns in Google Sheets Using the VLOOKUP Function
In this section, we will explore how to use the VLOOKUP function in Google Sheets to compare two columns. VLOOKUP is a powerful function that accomplishes the same task, but instead of returning the row number or a boolean value, it returns a “not found” error when the search key value cannot be found in both columns.
Here are the steps to follow:
- Write the VLOOKUP function.
- Enter the search criterion for the cell number.
- Pass the range in which you want to search for your key ($ Notation).
- Pass the index as 1.
- Pass TRUE if you want to retrieve only exact matches, and 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 – Unleashing the Power of Comparison
As you can see, Google Sheets offers a myriad of ways to compare data. You can adapt these methods to perform powerful data comparisons based on your specific needs. The applications can range from simply organizing your record collection inventory to examining a company’s product range.
To make your spreadsheet more organized, discover FileDrop. FileDrop is a sidebar application for Google Sheets and Docs that allows you to insert any type of file you need into the cells of your Google spreadsheets. Start your free trial today!
For more tips and tricks, visit Crawlan.com.
