How to Compare and Highlight Missing Data Between Two Excel Databases in WPS Spreadsheets
Question details
The user needs to compare two CSV or Excel datasets with different structures, find missing supplier information in the destination database, and highlight blank or mismatched values by using a reliable matching key, lookup formulas, and conditional formatting.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains two supplier databases in CSV or Excel format with different structures and naming conventions, and wants to compare Database B against Database A to spot missing supplier values across many fields.
- Observed behavior
- Missing or mismatched values in the destination database are not visually flagged, making manual comparison time-consuming and error-prone.
Open both workbooks in WPS Spreadsheets and identify one reliable matching key, such as Supplier ID or a standardized supplier name. If supplier names are inconsistent, standardize them with TRIM/CLEAN/UPPER or build a small cross-reference table before creating comparison formulas.
Use XLOOKUP Helper Columns and Conditional Formatting to Highlight Missing Data
Best for side-by-side database comparison when both tables share a reliable key and you need a clear, auditable highlight for missing or mismatched values.
XLOOKUP is available in current WPS Spreadsheets versions and includes an if-not-found argument, which makes missing values easy to flag without wrapping the formula in IFERROR. The helper-column approach also lets you compare one field at a time while keeping the original data intact.
Open Database B in WPS Spreadsheets and insert a blank column next to the first field you want to compare, such as Contract Value.
In the first data cell of the helper column, enter =XLOOKUP([@Supplier],DatabaseA[Supplier],DatabaseA[ContractValue],"Not found") and press Enter. Replace the table and column names with your actual ranges or use cell references if the source is in another sheet.
Drag the fill handle down to copy the formula for all supplier rows, then copy or adapt the formula for each additional field by changing the lookup and return columns.
Select the helper columns, go to the Home tab, click Conditional Formatting > Highlight Cells Rules > Equal To, enter "Not found", and choose a fill color such as light red.
With the same range selected, click Conditional Formatting > New Rule > Format only cells that contain, set the rule to Blanks, and choose an amber fill so truly blank return values are also highlighted.
Use IFERROR with VLOOKUP for Older WPS Spreadsheets Versions
Use this fallback when XLOOKUP is not available in your WPS Office build or when you need to share the workbook with users on older versions.
Compare Excel Databases and Highlight Missing Data with WPS Spreadsheets
WPS Spreadsheets gives you the same lookup and conditional formatting tools needed to compare two Excel or CSV databases, find missing supplier information, and highlight gaps across many fields. You can build the comparison in a few steps and automatically refresh it when source data changes.
- 1. Open both Excel or CSV files: Launch WPS Spreadsheets, click Menu > Open, and open Database A and Database B. If the files are CSV, use Data > Import to load them as spreadsheet tables.
- 2. Add helper columns: In Database B, insert a blank column next to each field you want to compare and name it clearly, such as "A Contract Value".
- 3. Enter a lookup formula: Use =XLOOKUP([@Supplier],DatabaseA[Supplier],DatabaseA[ContractValue],"Not found") or =IFERROR(VLOOKUP(B2,DatabaseA!$A:$D,3,FALSE),"Not found") depending on your WPS version.
- 4. Highlight missing values: Select the helper columns, go to Home > Conditional Formatting > Highlight Cells Rules > Equal To, enter "Not found", and choose a fill color. Add a blank-cell rule if needed.
- 5. Review and refresh: Filter or sort by the highlight color to review affected suppliers, and reopen or recalculate the workbook after updating the source data to refresh the comparison.
Frequently Asked Questions
Does WPS Spreadsheets support XLOOKUP for comparing two databases?
Yes. Current WPS Spreadsheets versions support XLOOKUP. If your build does not have XLOOKUP, use =IFERROR(VLOOKUP(lookup_value,table_array,col_index_num,FALSE),"Not found") to get the same missing-value flag.
How do I highlight blank values after a lookup in WPS Spreadsheets?
Select the helper columns, click Home > Conditional Formatting > New Rule > Format only cells that contain, choose Blanks, and set a fill color. You can also use a formula rule such as =LEN(TRIM(A2))=0 to catch cells that contain only spaces.
Can I compare two CSV files with different column structures in WPS Spreadsheets?
Yes. Open or import both CSV files into WPS Spreadsheets, place them in the same workbook or use sheet references, then apply XLOOKUP or VLOOKUP helper formulas. The comparison depends on a shared key such as Supplier ID or a standardized supplier name.
What should I do if supplier names do not match exactly between the two databases?
Standardize the names first. Use helper columns with =TRIM(CLEAN(UPPER(A2))) on both sides or create a cross-reference table that maps naming variations to a single canonical supplier name, then point your lookup formulas to that table.
Can I highlight differences for multiple columns at once?
Yes. Add a separate helper column for each field you want to compare, apply the same conditional formatting rule to all helper columns, or select the entire helper area before creating a single highlight rule for "Not found" and blanks.
