Function Problems

How to Cross-Check Tenant Payments Across Two Excel Data Sets in WPS Office

Natalie TaylorNatalie Taylor Nov 13, 2026 868 views

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.

How to Cross-Check Tenant Payments Across Two Excel Data Sets in WPS Office
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.
Before you start

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.

Solution 1Recommended

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.

1
Open both workbooks and identify columns

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.

2
Create a tenant key helper column

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.

3
Retrieve the comparison payment

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.

4
Mark matches and differences

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.

5
Copy the formulas down and review

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.

Tenant names may still differ: If the same tenant is written in different name orders or with aliases, a normalized key may still not match. In that case, create a manual alias mapping column or use a small sample workbook to tailor the formula.
WPS Spreadsheets Solution

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. 1. Open both tenant workbooks: Download and install WPS Office, then open both tenant payment Excel files in WPS Spreadsheets.
  2. 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. 3. Run the comparison: Use Formulas > Insert Function > XLOOKUP or VLOOKUP to pull the payment from the second dataset into the first.
  4. 4. Highlight differences: Add an IF formula to compare the original payment with the returned value and mark rows as Match or Difference.
  5. 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.
Cross-checks tenant payments with XLOOKUP, VLOOKUP, TRIM, CLEAN, and UPPER inside WPS SpreadsheetsOpens and saves .xlsx, .xls, and .csv files with full Microsoft Excel compatibilityFamiliar ribbon, formulas, and workbook structure make migration from Excel simpleFree lightweight Office suite for Windows, macOS, Android, and iOS
microsoft office alternative - wps office

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.