compare two excel lists for matches

Compare Excel Lists for Matches

By Michael Clark
AD

When you're working with large datasets in Excel, you might often find yourself in need of comparing two lists to find matches or discrepancies. This can be a laborious task if done manually, but with the right techniques, you can streamline this process efficiently. Let's explore how to compare two Excel lists for matches using a few built-in functions and tools.

Before we dive into the methods, ensure that your lists are well-structured with consistent formatting. Remove any leading, trailing, or extra spaces, and convert text to lowercase or uppercase to eliminate variances due to case sensitivity. Let's get started.

Using Conditional Formatting for Color-Coded Matches

Conditional formatting is a user-friendly tool that can help you identify matches quickly. It color-codes cells based on given rules, making the process more intuitive.

AD
Compare Two Lists in Excel
Compare Two Lists in Excel
Excel: Compare Two Columns for Matches and Differences
Excel: Compare Two Columns for Matches and Differences
Compare Two Lists in Excel for Matches (All Methods and Uses) - ExcelDemy
Compare Two Lists in Excel for Matches (All Methods and Uses) - ExcelDemy

Here's how to apply it:

Step 1: Apply Conditional Formatting

Select the range you want to compare (e.g., A2:A100). Then, click on 'Home' > 'Conditional Formatting' > 'New Rule'.

Excel – Using VLOOKUP to compare two lists
Excel – Using VLOOKUP to compare two lists
Find Duplicates in Excel Instantly | Smart Formula Trick You Must Know 💥 | Excel Tips for Beginners
Find Duplicates in Excel Instantly | Smart Formula Trick You Must Know 💥 | Excel Tips for Beginners
Compare Two Lists in Excel
Compare Two Lists in Excel

Step 2: Set the Rule

In the 'New Formatting Rule' dialog box, select 'Use a formula to determine which cells to format', and enter the formula: "=COUNTIF($B$2:$B$100, A2)>0". This counts how many times the value in column A appears in column B.

Step 3: Choose the Formatting

Click on the formatting control (e.g., fill color) to specify how you want to format the cells that match. Click 'OK', and the comparison is done.

AD

This method allows you to see matches at a glance, but it doesn't provide you with a list of matches or discrepancies. For that, we'll use Excel's built-in functions.

How To Compare Two Lists in Excel With 10 advance Methods
How To Compare Two Lists in Excel With 10 advance Methods
Compare Excel Lists Tool, Find Duplicates, Missing Items, Changes (Excel, Google Sheets)
Compare Excel Lists Tool, Find Duplicates, Missing Items, Changes (Excel, Google Sheets)
two screens showing the rows in excel
two screens showing the rows in excel
How to Compare Two Columns in Excel for Match (8 Easy Ways) - ExcelDemy
How to Compare Two Columns in Excel for Match (8 Easy Ways) - ExcelDemy

Using VLOOKUP, XLOOKUP, or INDEX MATCH for Match Identification

The VLOOKUP, XLOOKUP (Excel 365/Mac), and INDEX MATCH functions can help you find exact or approximate matches in a list. Here's how to use them:

VLOOKUP and XLOOKUP

Assume your lists are in columns A and B. To find matches in column B, enter this formula in cell C2 and drag down: ="match found" if XLOOKUP(A2, B:$B, "match found"), "". This will show 'match found' if the value in column A exists in column B.

AD

INDEX MATCH

If you want to find an exact match, use the INDEX MATCH double function: =IFERROR(INDEX(B$2:$B$100, MATCH(A2,$A$2:$A$100,0)), "No match"). This will display the corresponding value if a match is found in column B, or "No match" if no match is found.

These formulas give you a list of matches or discrepancies, which can be easily analyzed or filtered for further use.

Using Remove Duplicates and Show Formulas

Combine Remove Duplicates and Show Formulas features to quickly identify mismatches. Here's how:

AD

Step 1: Show Formulas

Set your cell display to show formulas (View > Formulas > Show Formulas). Enter a unique value (e.g., 9999) in column C next to your first list (column A).

Step 2: Remove Duplicates

Select columns A and C, go to Data > Remove Duplicates. In the dialog box, select only column A (remove the checkmark from column C), and click OK. Any cells in column A that do not match elsewhere will now show '9999', clearly indicating a mismatch.

This method also provides a list of mismatches, with results ready for analysis or next steps.

Comparing two Excel lists for matches doesn't have to be a complex or time-consuming task. Using a combination of Excel's built-in functions and tools, you can efficiently identify matches and discrepancies. With practice, these methods will become second nature, making your data analysis more efficient and less prone to errors. Happy comparing!

AD