Formula Errors

Fix HLOOKUP #N/A Errors for Identical Values in Spreadsheets

Aamir Naveed Akram Oct 9, 2026 869 views

Question details

The exact-match HLOOKUP formula returns an #N/A error because the underlying decimal precision of the values differs, even though the displayed numbers look identical.

How to Fix HLOOKUP #N/A Errors When Values Appear Identical

Product: Spreadsheet
Device & OS: not provided
Scenario: Attempting to retrieve data using exact-match HLOOKUP or XLOOKUP formulas on numeric values.
Observed behavior: The formula fails and returns an #N/A error despite the lookup value and the target array value appearing visually identical in their respective cells.

Before you start

Increase the decimal places of your lookup values and source data cells using the formatting toolbar to reveal any hidden precision differences before modifying your formulas.

Solution 1Recommended

Wrap Formulas in the ROUND Function

Standardize the precision of both your lookup value and the lookup array by wrapping the existing calculations in the ROUND function.

When cells are formatted to display a specific number of decimal places, the underlying stored value may still contain microscopic differences (e.g., 31768.49001 vs. 31768.49). Exact matches will always fail unless these underlying values are mathematically identical.

Edit the lookup value formula

Select the cell containing your lookup value and wrap its existing formula in the ROUND function, such as =ROUND(your_formula, 2).

Update the lookup array

Apply the same ROUND function to the source data cells in your lookup array to ensure both datasets share the exact same precision.

Apply exact match in HLOOKUP

Ensure your HLOOKUP formula uses FALSE as its final argument (e.g., =HLOOKUP(lookup_value, array, row, FALSE)) and press Enter to see the corrected result.

Precision Matched: Once both values are rounded to the exact same decimal place, the HLOOKUP formula will successfully find the exact match.

Solution 2

Use XLOOKUP with the ROUND Function

Replace HLOOKUP with the modern XLOOKUP function, combining it with the ROUND function for robust exact matching.

Efficient Formula Troubleshooting

Resolve Formula Errors Quickly in WPS Spreadsheet

WPS Spreadsheet offers powerful formula auditing tools and full support for advanced lookup functions like HLOOKUP, XLOOKUP, and ROUND, making it easy to fix precision errors and manage complex datasets.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing the #N/A errors.
2. Use Error Checking: Navigate to the 'Formulas' tab and click 'Error Checking' to quickly locate problematic HLOOKUP formulas.
3. Evaluate the formula: Click 'Evaluate Formula' to step through the calculation and identify hidden decimal precision differences.
4. Apply the ROUND function: Edit your formulas directly in the Formula Bar to add the ROUND function, ensuring exact matches.

Fully compatible with Microsoft Excel formulas and .xlsx files.Built-in Error Checking and Evaluate Formula tools for easy debugging.Native support for advanced XLOOKUP and ROUND functions.Lightweight, fast, and free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Why does HLOOKUP return #N/A when numbers look identical?

Spreadsheets can store numbers up to 15 decimal places of precision. Cell formatting only changes how a number is displayed on the screen, not its underlying stored value. If the hidden decimals differ, exact-match lookup formulas will fail.

Can I fix this by changing the cell format to show fewer decimals?

No, decreasing the displayed decimal places using cell formatting does not change the actual value stored in the cell's memory. You must use the ROUND function to mathematically alter the underlying data.

Does XLOOKUP have the same exact-match issue with decimals?

Yes. Even though XLOOKUP is a more modern and flexible alternative to HLOOKUP, its default exact-match behavior still requires the underlying numeric values to match perfectly. You may still need to incorporate the ROUND function.

Excel & Data Problems