Power Query Problems

How to Compare Two Large Excel Spreadsheets to Find Matching Contacts

Huma Ashraf Ch Oct 1, 2026 868 views

Question details

The user needs to cross-reference a CRM workbook containing over 8,000 contacts with a separate conference attendee list of 2,600 records to identify matching individuals.

How to Compare Two Large Excel Spreadsheets to Find Matching Contacts

Product: Excel
Device & OS: not provided
Scenario: Comparing two large, standalone datasets stored in separate files with similar but non-identical columns.
Observed behavior: Finding intersecting records between two heavy files efficiently without manually copying data.

Before you start

Ensure both Excel files are saved in a known folder and identify a unique matching key between them, such as an email address or a concatenated column of First Name, Last Name, and Company.

Solution 1Recommended

Use Power Query to Merge Data from Both Spreadsheets

Power Query is the most robust method for importing, standardizing, and comparing large datasets from separate files without opening them simultaneously.

Power Query can easily handle thousands of rows across multiple files. By using an 'Inner Join', you can command Excel to only display rows where the matching key exists in both the CRM workbook and the attendee list.

Import the first workbook

Open a new Excel workbook. Navigate to the Data tab on the ribbon, click Get Data > From File > From Workbook, and select your CRM file. Load it as a connection.

Import the second workbook

Repeat the previous step to import the conference attendee list, ensuring it is also loaded as a connection only.

Merge the queries

Go to Data > Get Data > Combine Queries > Merge. A dialog box will appear.

Select the matching criteria

Select the CRM table from the first dropdown and the Attendee table from the second. Click on the column containing the shared matching key (e.g., Email Address) in both tables.

Choose Inner Join

Under 'Join Kind', select 'Inner (only matching rows)'. Click OK to load the Power Query Editor, expand any additional columns you want to view, and click Close & Load to output the matches to your worksheet.

Use Power Query to Merge Data from Both Spreadsheets

Solution 2

Use the COUNTIF Function for a Simple Check

If you only need to know whether a contact from the attendee list exists in the CRM database, a COUNTIF formula provides a quick True/False check.

Efficient Data Management with WPS

Easily Compare Large Spreadsheets with WPS Office

WPS Spreadsheet provides advanced data processing capabilities, including full support for cross-workbook VLOOKUP, COUNTIF, and robust data consolidation tools, making it exceptionally easy to manage thousands of CRM contacts seamlessly.

1. Open your files in tabs: Launch WPS Spreadsheet and open both your CRM file and Attendee list. WPS uses a convenient multi-tab view within a single window, making switching between files instant.
2. Set up a comparison column: Select the empty column next to your attendee data in the Attendee workbook.
3. Use cross-file formulas: Enter the formula =COUNTIF([CRM.xlsx]Sheet1!$A:$A, A2) to check for matching email addresses across the files.
4. Filter for matches: Select the column header, go to the Data tab, click Filter, and check only the values greater than 0 to instantly reveal all matching contacts.

Handles massive datasets with over 10,000 rows without freezing or lagging.100% compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Built-in advanced formulas for cross-workbook referencing.Free, lightweight, and features an intuitive tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

What is the best matching key to use when comparing contact lists?

An email address is generally the best unique identifier. If emails are unavailable or incomplete, concatenating the First Name, Last Name, and Company Name into a single new column (using the & operator) is a highly reliable alternative.

Why does my COUNTIF formula show a #VALUE! error when the other file is closed?

Standard COUNTIF and SUMIF functions that reference external workbooks require the source file to remain open in the background to evaluate correctly. If you need the results to persist when closed, copy the formula results and paste them as values (Paste Special > Values).

Can I use VLOOKUP or XLOOKUP instead of COUNTIF to compare spreadsheets?

Yes. While COUNTIF simply tells you if a match exists, VLOOKUP or XLOOKUP can actively pull in additional data (like Phone Number or Member Status) from the CRM file directly into the attendee list based on the shared matching key.

Excel & Data Problems