Find Common Values From Two List In Excel
Find Common Values From Two List In Excel @BrainUpp
Navigate to the "Home" option and select duplicate values in the toolbar. Next, navigate to Conditional Formatting in Excel Option. A new window will appear on the screen with options to select "Duplicate" and "Unique" values. You can compare the two columns with matching values or unique values.
How to Compare Two Columns in Excel
Compare Two Columns For Exact Row Match
This one is the simplest form of comparison. In this case, you need to do a row by row comparison and identify which rows have the same data and which ones does not.
Example: Compare Cells in the Same Row
Below is a data set where I need to check whether the name in column A is the same in column B or not.
If there is a match, I need the result as “TRUE”, and if doesn’t match, then I need the result as “FALSE”.
The below formula would do this:
=A2=B2
Example: Compare Cells in the Same Row (using IF formula)
If you want to get a more descriptive result, you can use a simple IF formula to return “Match” when the names are the same and “Mismatch” when the names are different.
=IF(A2=B2,"Match","Mismatch")
Note: In case you want to make the comparison case sensitive, use the following IF formula:
=IF(EXACT(A2,B2),"Match","Mismatch")
With the above formula, ‘IBM’ and ‘ibm’ would be considered two different names and the above formula would return ‘Mismatch’.
Example: Highlight Rows with Matching Data
If you want to highlight the rows that have matching data (instead of getting the result in a separate column), you can do that by using Conditional Formatting.
Here are the steps to do this:
Select the entire dataset.
Click the ‘Home’ tab
In the Styles group, click on the ‘Conditional Formatting’ option.
From the drop-down, click on ‘New Rule’.
In the ‘New Formatting Rule’ dialog box, click on the ‘Use a formula to determine which cells to format’.
In the formula field, enter the formula: =$A1=$B1
Click the Format button and specify the format you want to apply to the matching cells.
Click OK.
This will highlight all the cells where the names are the same in each row.
Compare Two Columns and Highlight Matches
If you want to compare two columns and highlight matching data, you can use the duplicate functionality in conditional formatting.
Note that this is different than what we have seen when comparing each row. In this case, we will not be doing a row by row comparison.
Example: Compare Two Columns and Highlight Matching Data
Often, you’ll get datasets where there are matches, but these may not be in the same row.
Note that the list in column A is bigger than the one in B. Also some names are there in both the lists, but not in the same row (such as IBM, Adobe, Walmart).
If you want to highlight all the matching company names, you can do that using conditional formatting.
Here are the steps to do this:
Select the entire data set.
Click the Home tab.
In the Styles group, click on the ‘Conditional Formatting’ option.Click on Conditional Formatting
Hover the cursor on the Highlight Cell Rules option.
Click on Duplicate Values.Select Duplicate Values in Conditional Formatting
In the Duplicate Values dialog box, make sure ‘Duplicate’ is selected.Duplicate in Conditional Formatting
Specify the formatting.Specify the formatting in conditional formatting
Click OK.
Example: Compare Two Columns and Highlight Mismatched Data
In case you want to highlight the names which are present in one list and not the other, you can use the conditional formatting for this too.
Select the entire data set.
Click the Home tab.
In the Styles group, click on the ‘Conditional Formatting’ option.Click on Conditional Formatting
Hover the cursor on the Highlight Cell Rules option.
Click on Duplicate Values.Select Duplicate Values in Conditional Formatting
In the Duplicate Values dialog box, make sure ‘Unique’ is selected.Select Unique to highlight differences
Specify the formatting.Specify the formatting to highlight differences in two columns
Click OK.
Related searches
compare two columns in excel
vlookup compare two columns
how to compare two columns in excel for duplicates
excel compare two columns for missing values
excel formula to compare two columns and return a value
excel match two columns and output third
excel compare two lists and return differences
how to compare two columns in excel and count matches
Watch video Find Common Values From Two List In Excel online without registration, duration hours minute second in high quality. This video was added by user Brain Up 25 November 2022, don't forget to share it with your friends and acquaintances, it has been viewed on our site 92,745 once and liked it 2.1 thousand people.