How to Cross-Check Tenant Payments Across Two Excel Data Sets in WPS Office
Question details
The user needs to compare tenant payment amounts between two messy Excel data sources and identify whether each tenant pays the same amount, even though the tenant and payment columns or workbook structure are not specified.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has two separate Excel data sources containing tenant payment records and wants to cross-check whether tenants paid the same amount in both sources.
- Observed behavior
- The tenant identifiers and payment columns are messy or unstated, so a direct lookup cannot be run without cleaning the data and creating a consistent tenant key.
Open both tenant payment workbooks in WPS Spreadsheets and note the sheet names and the exact column letters for tenant names or IDs and payment amounts. If the tenant identifiers are text, plan to create a helper column for a normalized key using TRIM, CLEAN, and UPPER before running any lookup.
Clean Tenant Keys and Compare Payments with XLOOKUP in WPS Spreadsheets
Use this when tenant names or IDs contain spacing, capitalization, or non-printable character differences and you need a reliable payment match.
This method creates a normalized tenant key in both datasets so XLOOKUP compares the same text value. It works best when the tenant identifier is in a single column and the payment columns can be identified in each workbook.
In WPS Spreadsheets, open the two Excel files or sheets that contain tenant payment records. Note the sheet names and the exact column letters for tenant name or ID and payment amount.
In each dataset, add a new column next to the tenant name or ID. In the first data row, enter =UPPER(TRIM(CLEAN(A2))) if the tenant is in A2, then drag the fill handle down the column.
In the primary dataset, add a comparison column and select the first data cell. Go to Formulas > Insert Function, search for XLOOKUP, and fill the lookup_value, lookup_array, and return_array boxes using the second dataset's key and payment columns.
In another column, enter =IFERROR(IF(B2=D2,"Match","Difference"),"Not found") to compare the original payment in B2 with the returned payment in D2.
Select the formula cells and double-click the fill handle to apply them to all tenants, then filter or sort the comparison column to review matches, differences, and tenants not found.
Fallback Lookup Using VLOOKUP or INDEX/MATCH for Older WPS Versions
Use this if XLOOKUP is not available in your WPS Spreadsheets version or you prefer a lookup formula that also works in older Excel versions.
Cross-Check Tenant Payments in WPS Spreadsheets Without Switching Tools
Use WPS Spreadsheets to open both Excel data sources, clean tenant names with TRIM, CLEAN, and UPPER, and compare payment amounts with XLOOKUP or VLOOKUP from one familiar spreadsheet workspace.
- 1. Open both tenant workbooks: Download and install WPS Office, then open both tenant payment Excel files in WPS Spreadsheets.
- 2. Build clean tenant keys: Insert helper columns in both workbooks and enter =UPPER(TRIM(CLEAN(A2))) to normalize tenant names or =TRIM(A2) for numeric IDs.
- 3. Run the comparison: Use Formulas > Insert Function > XLOOKUP or VLOOKUP to pull the payment from the second dataset into the first.
- 4. Highlight differences: Add an IF formula to compare the original payment with the returned value and mark rows as Match or Difference.
- 5. Save and share: Save the workbook as .xlsx so it remains compatible with Microsoft Excel and share it with your accountant or property manager.

Frequently Asked Questions
How do I clean tenant names in WPS Spreadsheets so they match?
Use helper columns with formulas such as =UPPER(TRIM(CLEAN(A2))) to remove extra spaces, non-printable characters, and capitalization differences. Copy the formula down both datasets before running the lookup.
Can XLOOKUP compare two spreadsheets in WPS Office?
Yes. WPS Spreadsheets supports XLOOKUP. Go to Formulas > Insert Function, search for XLOOKUP, and select the lookup value, lookup range, and return range from the two sheets.
What should I do if XLOOKUP returns #N/A for a tenant?
Wrap the formula with IFERROR to return "Not found" instead of #N/A. Then check whether the tenant key differs because of name order, aliases, punctuation, or a missing record in the second dataset.
How do I match payments when tenant names are in different orders?
Create a manual alias mapping column or split the name into separate last name and first name helper columns, then concatenate them into a consistent key before comparing the payment amounts.
Does WPS Spreadsheets preserve Excel formulas and formatting?
Yes. WPS Spreadsheets opens and saves .xlsx workbooks while preserving formulas, formatting, conditional formatting, and workbook structure compatible with Microsoft Excel.




