How to Compare Two Excel Tables and Find Key or Value Differences
Question details
The user needs to compare two Excel tables containing keys and values to quickly identify missing keys or mismatched values between them.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two related datasets to audit records, finding discrepancies such as differing values for the same key, or identifying keys that exist in only one of the tables.
- Observed behavior
- Extracting the exact rows where keys are missing or values differ into a consolidated list, avoiding manual line-by-line comparison.
Ensure both tables have standardized column headers (e.g., 'name' and 'value') and format your data as official Excel Tables (Ctrl+T) so you can use dynamic structured references in your formulas.
Use Dynamic Array Formulas (LET, FILTER, VSTACK)
Combine modern Excel functions to dynamically extract mismatched values and missing rows without macros.
This approach uses the LET function to define variables for two separate checks: mismatched rows in Table 1 and missing keys in Table 2. It then stacks them together seamlessly using VSTACK.
Click on an empty cell where you want the list of differences to appear. Ensure there is enough empty space below and to the right for the data to spill.
Type the following formula: =LET(p1,FILTER(Table1,BYROW(Table1[name],LAMBDA(x,XLOOKUP(x,Table2[name],Table2[value],"")))<>Table1[value],""),p2,IFERROR(FILTER(Table2,ISERROR(XMATCH(Table2[name],Table1[name]))),""),VSTACK(p1,p2))
Press Enter. The first part of the result will return rows from Table1 whose values differ from Table2. The second part will return keys found exclusively in Table2.
Use Power Query (Left-Anti Join)
Ideal for larger datasets where complex array formulas might slow down performance.
Use a Python Script for Dictionary Comparison
A programmatic approach for users analyzing data outside of the spreadsheet environment.
Compare Tables Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like XLOOKUP, FILTER, and VSTACK, making it incredibly easy to compare complex datasets without switching tools.
- 1. Open your datasets in WPS: Launch WPS Spreadsheet and open the workbook containing the two tables you want to compare.
- 2. Format as Tables: Select each data range and press Ctrl+T to format them as official tables, naming them 'Table1' and 'Table2'.
- 3. Apply the comparison formula: In an empty cell, paste the LET and VSTACK formula to instantly spill the differences onto your sheet.
Frequently Asked Questions
Why does my dynamic array formula return a #NAME? error?
A #NAME? error typically occurs if your spreadsheet software does not support modern functions like LET, VSTACK, or XLOOKUP. You will need to upgrade to Microsoft 365 or use a modern alternative like WPS Office.
Can I use conditional formatting instead of a formula to highlight differences?
Yes. You can select both columns of values, navigate to the Home tab, click Conditional Formatting > Highlight Cells Rules > Duplicate Values, and change the dropdown from 'Duplicate' to 'Unique' to visually highlight non-matching data.
How do I compare tables if I have multiple columns instead of just key and value?
If you are using Power Query, you can select all relevant columns when configuring the Merge/Join. If using formulas, you can concatenate the values of multiple columns to create a single unique identifier string for comparison.
