Forum Discussion

soarhevn's avatar
soarhevn
Copper Contributor
Feb 15, 2023
Solved

XLOOKUP returns #N/A using lookup_value from a formula, but not the same value directly

I have two columns in a lookup table:

0.00000.0075
0.05000.0050
0.10000.0025
0.11000.0000

 

Given the table is at A1:A4,B1:B4.

 

The formula XLOOKUP(0.1,A1:A4,B1:B4) returns 0.0025 as expected.

 

The formula XLOOKUP(0.75-0.65,A1:A4,B1:B4) returns #N/A. 0.0025 was expected.

 

All columns formatted as number.

Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Feb 15, 2023

    soarhevn That's due to minute rounding differences. Much is written about it. Mostly a lot of technical language on how computers perform calculations internally. When you notice it, use ROUND to fix it.

     

    Change the formula like this: =XLOOKUP(ROUND(0.75-0.65,4),A1:A4,B1:B4)

     

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 15, 2023

    soarhevn That's due to minute rounding differences. Much is written about it. Mostly a lot of technical language on how computers perform calculations internally. When you notice it, use ROUND to fix it.

     

    Change the formula like this: =XLOOKUP(ROUND(0.75-0.65,4),A1:A4,B1:B4)

     

     

Resources