How to Compare Values Between Two Excel Worksheets with VLOOKUP in WPS Office
Question details
The user needs to compare an Amount column between two Excel worksheets for different years and identify which values stayed the same or changed using VLOOKUP.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has two worksheets containing data from different years and wants to compare the Amount column by matching common IDs and calculating the difference.
- Observed behavior
- A VLOOKUP formula retrieves the matching amount from the second worksheet; subtracting the current amount returns 0 for unchanged values or a non-zero difference for changed values.
Make sure both worksheets contain a common identifier column, such as ID or name, and that the lookup column is to the right of the identifier in the source worksheet.
Compare Two Worksheet Amount Columns with VLOOKUP in WPS Office
Use this solution when both worksheets share a common identifier and you need a numeric difference for each row.
The formula looks up the identifier from the current row in the second worksheet and subtracts the current amount. A zero result means the values are the same; any other result shows how much the value changed.
Open the WPS Office workbook that contains the two worksheets you want to compare, such as Sheet1 and Sheet2.
Click the cell where you want the difference to appear, for example G2 in Sheet1.
Type =VLOOKUP(A2,Sheet2!A2:C17,3,FALSE)-C2 and press Enter. Replace A2 with the common identifier cell, Sheet2!A2:C17 with the second worksheet range, 3 with the amount column index, and C2 with the current amount cell.
Drag the fill handle down the column to apply the formula to the remaining rows. Rows showing 0 mean the amount stayed the same; other results show the change.
Label Matching and Changed Amounts with IF and VLOOKUP
Use this solution when you prefer a clear Same or Changed label instead of a numeric difference.
Compare Worksheets Easily with WPS Office
WPS Office Spreadsheets supports Excel-compatible VLOOKUP and subtraction formulas, so you can compare two worksheets, identify unchanged or changed amounts, and keep working in a familiar interface.
- 1. Open your workbook in WPS Office: Launch WPS Office and open the workbook containing the two worksheets you want to compare.
- 2. Select the comparison cell: Click the cell in the first worksheet where you want the difference or label to appear.
- 3. Enter the VLOOKUP formula: Type =VLOOKUP(A2,Sheet2!A2:C17,3,FALSE)-C2 or =IF(VLOOKUP(A2,Sheet2!A2:C17,3,FALSE)=C2,"Same","Changed") and press Enter.
- 4. Fill the formula down: Drag the fill handle down the column to compare all rows.
- 5. Review zero and non-zero results: Zero means the amount stayed the same; any non-zero result shows how much it changed, while Same or Changed labels give a quick status.
Frequently Asked Questions
Why does my VLOOKUP comparison return #N/A?
This usually happens when the identifier in the first worksheet is not found in the first column of the second worksheet range. Check that both ID columns contain the same values and that the table range starts with the identifier column.
What does a 0 result mean when comparing amounts?
A 0 after subtracting the current amount from the VLOOKUP result means the two amounts are exactly the same. A positive or negative number shows that the amount increased or decreased.
Can I compare more than one column at a time?
Yes. Change the col_index_num in VLOOKUP to return a different column from the second worksheet, then compare it with the corresponding column in the first worksheet using subtraction or IF.
How do I compare values if the worksheets are in different WPS workbooks?
You can reference another workbook by including its file name in the formula, such as =VLOOKUP(A2,[Book2.xlsx]Sheet2!A2:C17,3,FALSE)-C2. Keep the source workbook open or update the path when WPS prompts.
Why should I use FALSE in VLOOKUP for this comparison?
FALSE forces an exact match, so WPS only returns an amount when the identifier matches exactly. If you use TRUE or omit it, you may get an approximate match and incorrect comparison results.
