Find Common Values in Two Excel Sheets Easily

When dealing with data from multiple sources, one of the most common tasks is identifying common values between two or more Excel sheets. Whether you're comparing customer lists, inventory, or any datasets, finding overlapping information quickly and accurately is essential. This article will guide you through various methods to easily find common values in two Excel sheets, ensuring you have the tools to enhance your data analysis skills.

Using the VLOOKUP Function

The VLOOKUP function is a classic tool for looking up values in Excel. Here’s how you can use it to find common values:

• Ensure that the data in both sheets has a common column where the comparison will be made.
• In the first sheet, add a new column next to the lookup value.
• Enter the following formula in the new column:

  =IF(ISERROR(VLOOKUP(A2,Sheet2!A:A,1,FALSE)),"Unique","Common")

This formula checks if the value in cell A2 exists in column A of Sheet2. If it doesn't (an error), it marks the cell as "Unique." If it does, it's labeled "Common."

Steps:

1. Select the cell where you want to display the result.
2. Enter the formula, adjusting the cell references and sheet names as necessary.
3. Drag the formula down to apply it to all rows in your data set.

📌 Note: Always make sure the lookup column in both sheets is sorted if using approximate match in VLOOKUP, otherwise, use exact match for accuracy.

Conditional Formatting for Visual Comparison

Excel’s conditional formatting can visually highlight common values across two sheets, making it easier to spot matches at a glance:

Steps:

1. Select the range in one sheet you want to compare.
2. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. In the formula box, enter the following:

   =COUNTIF(Sheet2!A:A,A1)>0

4. Choose a format to highlight the common values (e.g., bold, color).
5. Click “OK” to apply the rule.

Now, any cell with a value that also appears in Sheet2's column A will be highlighted.

🔎 Note: This method visually identifies matches but does not alter your data, making it great for presentations or quick checks.

Using Power Query to Merge Data

Power Query provides advanced data manipulation capabilities, including merging datasets:

Steps:

1. Go to Data > Get Data > From Other Sources > Blank Query.
2. In the Query Editor, click Advanced Editor and paste the following M code:

   let
       Source1 = Excel.CurrentWorkbook(){[Name=“Sheet1”]}[Content],
       Source2 = Excel.CurrentWorkbook(){[Name=“Sheet2”]}[Content],
       Merged = Table.NestedJoin(Source1,{“ColumnToMatch”},Source2,{“ColumnToMatch”},“NewColumn”,JoinKind.Inner),
       Expanded = Table.ExpandTableColumn(Merged, “NewColumn”,{“ColumnToMatch”}, {“CommonValue”})
   in
       Expanded
   

3. Replace “ColumnToMatch” with the actual name of the column you’re comparing. Load the query back into Excel.

This process will create a new sheet with only the rows where values match between Sheet1 and Sheet2.

🔄 Note: This method requires basic knowledge of Power Query, but it offers powerful merging capabilities for large datasets.

Conclusion

Finding common values in Excel sheets doesn’t have to be a cumbersome process. Whether you prefer the simplicity of VLOOKUP, the visual aid of conditional formatting, or the advanced merging capabilities of Power Query, Excel provides multiple pathways to achieve your goal. By understanding these methods, you can streamline your data comparison tasks, making your work more efficient and insightful. Remember, the choice of method largely depends on the size of your data, your familiarity with Excel functions, and the need for either quick analysis or detailed data merging.

Can VLOOKUP find multiple common values?

+

VLOOKUP is designed to look up a single value. However, by using an array formula or combining VLOOKUP with other functions like IF or COUNTIF, you can create a workaround to find multiple common values.

What are the limitations of conditional formatting for finding common values?

+

Conditional formatting can only highlight cells based on a given condition but does not provide a way to extract or manipulate the data directly. It’s excellent for visual identification but not for further data processing.

Is Power Query available in all Excel versions?

+

Power Query, or Get & Transform in newer versions, is available in Excel 2016 and later on Office 365 or standalone Office versions. It’s not available in Excel for Mac or earlier versions unless updated.