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

[soarhevn]

I have two columns in a lookup table:

0.0000	0.0075
0.0500	0.0050
0.1000	0.0025
0.1100	0.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.

[#2 · Accepted solution]

@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)

[#3]

Thank you! That worked.