The VLOOKUP function is the easiest function to compare the values of two columns and you can use it for the above example easily. It stands for "Vertical Lookup" and is used to search for a value in the leftmost column of a table and retrieve a corresponding value from a specified co...
All matched values of the two selected columns are displayed in a new column: Matches. Method 3 – Using a Macro to Extract Unique Values From Two Columns Extract unique values bycomparing two columns. In this section, I’ll show you how to do that using VBA macro. Two columns were adde...
You will get all the results as shown in this image. Method 3 – Applying Conditional Formatting to Compare Text in Two Columns for Matches and Differences 3.1 Finding Matches Steps: Go toHome>Conditional Formatting>New Rule. SelectUse a formula to determine which cells to formatoption and inse...
To compare two columns, use IF, ISERROR and MATCH in Excel. You can display the duplicates or the unique values.
Find and highlight the duplicates or matching values in two columns with Kutools for Excel If you have Kutools for Excel, with its Select Same & Different Cells function, you can quickly compare the data in two columns and select the duplicate cells in one column. What’s more, you can ...
Conditional formatting is used to compare two columns based on a condition. We can find the unique and duplicate values in the dataset, and it automatically highlights the cells as per the condition. We can also create a custom rule for conditional formatting in Excel. ...
In the rule description field, enter =$A1=$B1. (Why the dollar signs?) Click “Format…” to choose how to format cells that meet the condition. Click OK. Rows with matching values will be highlighted in the selected format. To compare two columns using case-sensitive conditional formattin...
2. Using COUNTIF to compare two columns Another way to find missing values is to use theCOUNTIF function. The COUNTIF function counts how many times a value appears in a range. For example, in this case, we count how many times a value from Column C is found in Column F. If the ...
STEP 1:Select both columns. STEP 2:Go toHome>Find & Select>Go To Specialor simply press keysCtrl + Gand SelectSpecialto open the Go To Special dialog box. STEP 3:SelectRow Differenceand ClickOK. And, Voila! All the values in List 2 that do not match the corresponding value in List ...
How to compare two columns in Excel for matches and differences Suppose you have 2 lists of data in Excel, and you want to find all values (numbers, dates or text strings) which are in column A but not in column B. For this, you can embed the COUNTIF($B:$B, $A2)=0 function ...