Fix VLOOKUP Approximate Match Returning the Wrong Range in WPS Spreadsheets
Question details
The user needs to correct a VLOOKUP approximate match that uses a calculated lookup value whose displayed rounded number hides decimal values, causing the wrong lookup range.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is using VLOOKUP with TRUE for an approximate match on a calculated value such as 1700, but the underlying cell contains hidden decimals.
- Observed behavior
- VLOOKUP matches a lower threshold instead of the expected approximate range because it uses the underlying numeric value rather than the rounded displayed value.
Confirm that the lookup value cell contains a calculation and check its underlying decimals by selecting the cell and viewing the formula bar; also verify that the first column of the lookup table is sorted in ascending order.
Wrap the Lookup Value with ROUND Inside VLOOKUP
Use this when the lookup cell contains a calculation and you want to keep the original cell unchanged.
VLOOKUP with TRUE compares the actual numeric value in the cell, not the displayed value. A value displayed as 1700 but stored as 1699.5 is still less than 1700 and falls into the lower bracket.
Click the cell containing the VLOOKUP formula in WPS Spreadsheets.
In the formula bar, replace the lookup value with ROUND(C8,0), for example: =VLOOKUP(ROUND(C8,0),'Lookup table',2,TRUE).
Press Enter to apply the formula and confirm that the approximate match returns the expected range.
Round the Source Calculation Before VLOOKUP
Use this when you can modify the lookup value cell directly, so all formulas referencing C8 use the rounded value.
Verify the Lookup Table Sort Order
Use this if the approximate match still returns unexpected results after rounding, because VLOOKUP TRUE requires ascending order.
Fix VLOOKUP Approximate Match by Rounding Hidden Decimals in WPS Spreadsheets
Use WPS Spreadsheets to round the lookup value or source calculation so VLOOKUP approximate match uses the intended threshold, while keeping formulas compatible with Microsoft Excel workbooks.
- 1. Open the workbook: Open the affected workbook in WPS Spreadsheets.
- 2. Select the VLOOKUP cell: Click the cell that contains the VLOOKUP formula.
- 3. Apply the ROUND fix: Change the formula to =VLOOKUP(ROUND(C8,0),'Lookup table',2,TRUE) in the formula bar.
- 4. Verify the result: Press Enter and confirm that the approximate match now returns the correct range.
- 5. Save the file: Save the workbook as .xlsx to retain compatibility with Microsoft Excel.
Frequently Asked Questions
Why does VLOOKUP approximate match return a lower value when the cell shows 1700?
Because VLOOKUP with TRUE uses the underlying numeric value, not the displayed value. A calculated result displayed as 1700 may actually be 1699.5 or contain other decimals, so it falls below the 1700 threshold and matches the lower range.
How do I round the lookup value without changing the original cell?
Wrap the lookup value inside the VLOOKUP formula with ROUND, for example: =VLOOKUP(ROUND(C8,0),'Lookup table',2,TRUE). This rounds the value only for the lookup.
Can I round the source calculation instead of the VLOOKUP formula?
Yes. Edit the lookup value cell and change the calculation to =ROUND(A8/B8,0) or similar. All formulas referencing that cell will then use the rounded value.
Does VLOOKUP approximate match require the lookup table to be sorted?
Yes. For VLOOKUP with TRUE to work correctly, the first column of the lookup table must be sorted in ascending order. You can sort it using the Data tab and Sort Ascending.
Will this rounded VLOOKUP formula work in Microsoft Excel?
Yes. WPS Spreadsheets uses the same VLOOKUP and ROUND functions, and the workbook can be saved as .xlsx to remain fully compatible with Microsoft Excel.
