Function Problems

How to Compare Loan Amounts Between Excel Sheets by Loan Number

Rana Garcia Sep 29, 2026 868 views

Question details

The user needs to compare loan amounts for specific loan numbers across different sheets to identify changes and missing data.

How to Compare Loan Amounts Between Excel Sheets by Loan Number

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Tracking mortgage processing data across multiple stages where loan numbers are verified against their corresponding amounts in different worksheets.
Observed behavior: The user wants to identify matching entries, detect missing records, and highlight changed loan amounts between two datasets.

Before you start

Ensure both worksheets contain a unique identifier column (like Loan Number) and that the cells are formatted identically (e.g., both as Text or both as Numbers) to prevent lookup errors.

Solution 1Recommended

Use XLOOKUP and IF Formulas to Compare Amounts

This method retrieves the corresponding loan amount from the second sheet using XLOOKUP, and uses logical IF tests to determine if the amounts match or if the record is missing.

This solution assumes you have two sheets: StageA and StageB. The Loan Number is in column A and the Amount is in column B on both sheets.

Retrieve the amount from the second sheet

In your StageA sheet, click on cell C2 and enter the formula: =XLOOKUP(A2,StageB!A:A,StageB!B:B,"Not Found"). Press Enter to pull the corresponding amount from StageB.

Calculate the difference

In cell D2, type the formula: =IF(C2="Not Found","Missing",B2-C2). This calculates the numerical variance between the two stages or flags the cell if the loan is missing.

Show the comparison status

In cell E2, input the formula: =IF(C2="Not Found","Missing in StageB",IF(ABS(D2)>0,"Amount Mismatch","Match")). This provides a clear, readable status for each loan number.

Apply formulas to the entire column

Select cells C2, D2, and E2. Double-click the small green square at the bottom-right corner of the selection to fill the formulas down to the end of your dataset.

Use XLOOKUP and IF Formulas to Compare Amounts

Formula Adjustment: If your worksheet tabs are named differently (e.g., 'Processing' instead of 'StageB'), make sure to update the sheet names in your formulas accordingly.

Solution 2

Use VLOOKUP as an Alternative for Older Versions

If you are using an older version of Excel that does not support the XLOOKUP function, you can combine VLOOKUP with IFERROR to achieve the same result.

WPS Spreadsheet Data Analysis

Seamlessly Compare Financial Data with WPS Spreadsheet

WPS Spreadsheet is a powerful and free tool that fully supports XLOOKUP, VLOOKUP, and complex logical formulas out of the box. You can easily compare large mortgage datasets, identify mismatched loan amounts, and handle cross-sheet calculations instantly.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your loan processing stages.
2. Enter the XLOOKUP formula: Select the target cell in your primary sheet and enter =XLOOKUP(A2,StageB!A:A,StageB!B:B,"Not Found").
3. Drag to fill: Use the fill handle to apply the lookup and comparison formulas across your entire dataset automatically.

Fully compatible with Microsoft Excel formulas and the .xlsx format.Supports modern functions like XLOOKUP without requiring an expensive subscription.Lightweight architecture ensures fast calculation even with massive financial datasets.100% free to download with an intuitive, tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does XLOOKUP return 'Not Found' even when the loan numbers look identical?

This is almost always due to formatting differences. For instance, StageA might have loan numbers formatted as Text, while StageB has them as Numbers. You can fix this by selecting the column, going to Data > Text to Columns, and clicking Finish to standardize the formatting.

Can I compare loan amounts across two entirely different workbooks?

Yes. You can reference another workbook in your XLOOKUP formula. Open both workbooks in your spreadsheet software, start typing the formula, and simply navigate to the other workbook to select the target columns. The file name will be automatically included in square brackets within your formula.

How can I automatically highlight mismatched loan amounts?

You can use Conditional Formatting. Select the column containing your final status (e.g., Column E). Go to the Home tab, click Conditional Formatting > Highlight Cells Rules > Equal To, type 'Amount Mismatch', and select a red fill to highlight those specific cells.

What if there are duplicate loan numbers in my dataset?

XLOOKUP and VLOOKUP will only return the first matching instance they find. If a single loan number appears multiple times and you need to compare total amounts, use the SUMIFS function instead to sum the amounts for each loan number before comparing them.

Excel & Data Problems