Formula Errors

How to Fix XLOOKUP Errors Caused by Floating-Point Precision in Excel

Chanuka Geekiyanage Oct 1, 2026 871 views

Question details

Users need to resolve #N/A or incorrect results when using XLOOKUP because the underlying floating-point values of calculated numbers differ slightly from their displayed values.

How to Fix XLOOKUP Errors Caused by Floating-Point Precision

Product: Excel
Device & OS: not provided
Scenario: Looking up numeric values resulting from calculations (such as multiplied decimal fractions) where the visible number appears identical to the lookup target, but the internal binary representation does not match.
Observed behavior: The XLOOKUP formula returns an error or an unexpected value because it defaults to an exact match, which fails due to microscopic discrepancies in how the floating-point numbers are stored.

Before you start

Verify if the lookup values are generated by formulas rather than typed manually, as calculations are the primary cause of microscopic floating-point decimal discrepancies.

Solution 1Recommended

Use XLOOKUP's Approximate Match Mode

Adjust the XLOOKUP formula parameters to accept the next smaller or larger value, bypassing the strict exact-match requirement that triggers floating-point errors.

By default, XLOOKUP searches for an exact match. By utilizing its fifth argument (match_mode), you can instruct the formula to fall back to the closest approximate value, effectively ignoring microscopic decimal precision differences.

Locate the formula

Select the cell containing the XLOOKUP formula that is returning the error.

Modify the if_not_found argument

Add a comma after your return_array. Enter your desired fallback value or error text for the fourth argument. For example, enter '1' if you want it to return 1 when absolutely nothing is found.

Set the match mode

Add another comma and enter '-1' for the fifth argument. This forces XLOOKUP to return the exact match or the next smaller item. The final formula will look similar to: =XLOOKUP(C2, Definitions!$B$2:$B$7, Definitions!$A$2:$A$7, 1, -1).

Apply and test

Press Enter to apply the formula. Verify that the unexpected error is resolved and the correct corresponding value is returned.

Formula Accuracy: Using approximate matches is often safer than changing global workbook settings because it only affects the specific formulas you modify.

Solution 2

Enable 'Set precision as displayed' Setting

Change Excel's calculation settings to permanently truncate stored numbers to match their visible screen formatting.

Use WPS Spreadsheet to Handle Complex Lookups

WPS Office offers a powerful Spreadsheet application fully equipped to handle advanced data functions, including XLOOKUP. It processes formulas with high accuracy and provides an intuitive interface to help you troubleshoot data precision errors quickly.

1. Download and Install: Get WPS Office for free from the official website and open WPS Spreadsheet.
2. Open Your Document: Open your existing Excel workbook (.xlsx) containing the faulty lookup formulas.
3. Edit the Formula: Select the cell with the error and easily modify the XLOOKUP arguments using the built-in formula syntax guide.
4. Apply Match Modes: Add '-1' as the match mode parameter and hit Enter to instantly resolve precision-based exact match failures.

Fully compatible with Microsoft Excel formulas, including XLOOKUP and ROUND.Offers reliable calculation engines to handle complex floating-point mathematics.Lightweight, fast, and completely free to use for everyday office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does a calculated number differ from the displayed number in Excel?

Computers process numbers using binary floating-point arithmetic (IEEE 754 standard). Certain decimal fractions cannot be represented exactly in binary, leading to microscopic differences (e.g., 0.01 might be stored as 0.010000000000001) that cause exact-match formulas to fail.

Can I fix XLOOKUP precision errors using the ROUND function?

Yes. Wrapping your lookup value in the ROUND function (e.g., =XLOOKUP(ROUND(C2, 2), ...)) forces the calculation to stop at a specific decimal place, effectively matching the underlying value and bypassing floating-point errors without changing workbook settings.

Is it safe to use 'Set precision as displayed' for all my workbooks?

No, it is generally not recommended as a default setting. Enabling this feature permanently deletes any hidden decimal data beyond the cell's visible formatting. This is dangerous for financial or scientific workbooks that rely on exact background calculations.

Excel & Data Problems