How to Compare Two Excel Tables and Highlight Mismatches in WPS Office
Question details
The user needs to compare two Excel tables using Column A as the primary key, ignore extra columns in the second table, preserve the first table's column order, and highlight matching and mismatching values with different conditional formatting colors.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has two Excel tables that share a primary key in Column A and wants a comparison output that updates visually instead of checking cell by cell.
- Observed behavior
- The desired result is a formula-driven output table that preserves Table 1's column order, ignores extra Table 2 columns, and colors matching and mismatching values separately.
Make sure both tables have a shared primary key in Column A and that the column headers you want to compare are spelled identically in both tables. If you plan to use XLOOKUP, verify that your WPS Office version supports it; otherwise use INDEX and MATCH or VLOOKUP with MATCH.
Create an Output Table with XLOOKUP and Conditional Formatting in WPS Office
Use this method when WPS Office supports XLOOKUP and you want to compare tables by primary key and column header before applying separate highlight colors.
This method preserves Table 1's column order because the output table is built from Table 1's headers. Extra columns in Table 2 are ignored because the formula looks up only the headers that exist in the output table.
Open the workbook containing the two tables, then click a blank cell where you want the comparison output table to begin.
Copy or type the primary key values from Table 1 into Column A of the output area, and enter Table 1's column headers in the same order across the first row.
Click the cell under the first Table 1 header, click the formula bar, and enter =XLOOKUP($A2,Table2_KeyRange,XLOOKUP(H$1,Table2_HeaderRange,Table2_DataRange)) then press Enter. Replace the ranges with your actual Table 2 references.
Select the formula cell and drag the fill handle across all headers and down all primary-key rows, or use Ctrl+C and Ctrl+V after checking the absolute references.
Select the output data range, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Create one rule with =H2=OriginalCell and one with =H2<>OriginalCell, replacing OriginalCell with the corresponding Table 1 cell, and choose different highlight colors.
Use VLOOKUP with MATCH or INDEX and MATCH for Older WPS Office Versions
Use this alternative when XLOOKUP is not available or when you need a formula approach that works in more Excel-compatible environments.
Compare Two Excel Tables and Highlight Mismatches Directly in WPS Office
WPS Office Spreadsheet lets you build a lookup-based comparison table that preserves Table 1's column order, ignores extra Table 2 columns, and uses conditional formatting to distinguish matches from mismatches without switching tools.
- 1. Open the workbook in WPS Office: Launch WPS Office Spreadsheet, open the workbook, and confirm that both tables share a primary key in Column A.
- 2. Build an output table from Table 1: In a blank area, create a new table using Table 1's primary key and column headers so the original column order is preserved.
- 3. Enter a lookup formula: Click the first output cell, click the formula bar, and enter an XLOOKUP, VLOOKUP with MATCH, or INDEX and MATCH formula to pull the matching value from Table 2.
- 4. Apply conditional formatting rules: Select the output range, go to Home > Conditional Formatting > New Rule > Use a formula, and create separate rules for matching and mismatching values with different colors.
- 5. Review and save the result: Fill the formula across the table, confirm that the highlight colors update as expected, and press Ctrl+S to save the workbook.
Frequently Asked Questions
Can I compare tables when the second table has extra columns?
Yes. The output table uses only the headers from Table 1, and the lookup formula matches each header explicitly, so extra columns in Table 2 are ignored.
What formula should I use if XLOOKUP is not available in my WPS Office version?
Use VLOOKUP with MATCH, such as =VLOOKUP($A2,Table2_Range,MATCH(H$1,Table2_Header_Range,0),FALSE), or use INDEX with two MATCH functions for the row and column.
How do I create two different colors for matches and mismatches?
Select the output range, click Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, then add one rule with =OutputCell=OriginalCell and another with =OutputCell<>OriginalCell, choosing different highlight colors.
Will this method preserve the first table's column order?
Yes. Because the output table is built from Table 1's headers and populated by header-specific lookup formulas, the original column order remains unchanged.
Can I use this with Microsoft Excel files in WPS Office?
Yes. WPS Office Spreadsheet supports .xlsx, .xls, and .csv files and Excel formulas, so you can open and edit the workbook without converting it.
