Fix VLOOKUP Failing with MROUND Decimal Results in Excel
Question details
The VLOOKUP formula fails to find decimal numbers generated by the MROUND function, returning an #N/A error despite the values appearing to match.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using the VLOOKUP function to search an array for a dynamically calculated decimal value returned by the MROUND function.
- Observed behavior
- VLOOKUP returns an #N/A error because the internal floating-point precision of the MROUND result does not exactly match the lookup array's underlying decimal value.
Verify that your lookup table values are formatted correctly as numbers and determine the exact number of decimal places required for an exact match.
Wrap the MROUND Result in a ROUND Function
Resolve floating-point precision discrepancies by explicitly rounding the MROUND result before passing it to the VLOOKUP function.
Spreadsheet software stores numbers in a binary floating-point format. This can create microscopic decimal discrepancies (for example, internally storing a calculation as 1.20000000000001 instead of exactly 1.2) when using functions like MROUND. Wrapping your formula in the ROUND function forces the internal value to precisely match your target value.
Select the cell containing your broken VLOOKUP formula to edit it in the formula bar.
Modify your MROUND calculation by nesting it inside a ROUND function. Set the decimal precision to match your lookup table. For example: =ROUND(MROUND(A1, 1.2), 1).
Place this newly rounded value as the first argument in your VLOOKUP. The updated syntax should look like: =VLOOKUP(ROUND(MROUND(A1, 1.2), 1), lookup_range, column_index, FALSE).
Press Enter to apply the updated formula and confirm that the #N/A error is resolved.
Round the Data in the Lookup Column
If the source data in your lookup column also suffers from floating-point errors, round those values to guarantee an exact match.
Solve Formula Precision Errors with WPS Spreadsheet
WPS Spreadsheet provides a fully compatible, robust calculation engine that supports all standard formulas, including VLOOKUP, ROUND, and MROUND, ensuring your data analysis is highly precise and error-free.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the VLOOKUP errors.
- 2. Edit the formula: Double-click the cell returning the #N/A error to access the formula bar.
- 3. Nest the functions: Modify your formula by combining ROUND and MROUND: =VLOOKUP(ROUND(MROUND(value, multiple), decimal_places), table_array, col_index, FALSE).
- 4. Calculate exact match: Press Enter. WPS Spreadsheet will calculate the precise match instantly without floating-point discrepancies.
Frequently Asked Questions
What causes floating-point errors in spreadsheet formulas?
Spreadsheet applications use the IEEE 754 standard for storing decimal numbers, meaning some fractional values cannot be represented perfectly in binary. This leads to minuscule discrepancies deep in the decimal tail, preventing exact-match functions from finding a target value.
Why does my VLOOKUP return #N/A when the numbers look identical?
When using VLOOKUP with an exact match (FALSE), the function compares the underlying stored value, not the visually displayed value. If a cell displays '1.2' but is internally stored as '1.200000000000001', VLOOKUP will not recognize them as a match.
Can I use ROUNDUP or ROUNDDOWN instead of ROUND with VLOOKUP?
Yes. Depending on your calculation logic, you can use ROUNDUP or ROUNDDOWN to control the output. Just ensure that the resulting decimal perfectly aligns with the exact target values stored in your lookup array.
