VLOOKUP- Bizarre Formatting Issue

[wiemsalot]

Alright Excel wizards, I need some VLOOKUP (or maybe the root of Excel) clarification. I've been using Excel for years and I'm thoroughly stumped.

I'm familiar with the idea that text and numbers are formatted differently, and formats must match for any lookup/search function to work correctly. No problem.

However, today I was trying to do something very simple. For example, VLOOKUP(1-F6, LOOKUP_RANGE, 2, FALSE). Returned an #N/A error. I double-checked ranges, checked formatting, the works. In this case, turns out that I needed to be using "NUMBERVALUE(1-F6)" instead of "VALUE(1-F6)". Okay, fair enough; I don't know the super technical differences but I can get on board with using it.

During my testing though, I stumbled across something bizarre. Depending on the value in cell F6, the VLOOKUP would sometimes return a result when doing VLOOKUP(VALUE(1-F6), LOOKUP_RANGE, 2, FALSE). I'm at a loss. Even though there was no formatting or anything special applied to the lookup range. Of course, triple-checking that the value does indeed exist.

My question is two-fold
1) In a fresh workbook, is NUMBERVALUE "safer" than using VALUE for looking up numbers?
2) Why on earth is there this seemingly random cut-off for these values I'm trying to lookup? See attached screenshot.

The screenshot is directly from a fresh workbook. No formatting applied. No other cells with data. The VLOOKUP and INDEX/MATCH formulas are what you'd expect them to be based on what you see.

Given that I can use NUMBERVALUE, I will continue doing that. But I am incredibly curious as to what is happening here.

Let me know if I can provide any other information that may help.

 

[Toadstool]

Hi Wiemsalot,

I believe you have fallen foul of the Floating Point Precision challenge.
Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?” - Microsoft 365 Blog

You can avoid this by either changing your calculated value and Lookup column to text, using TEXT or by using ROUND for your calculated and Lookup values.

 

[wiemsalot]

Hey Toadstool,

Thanks a ton for the quick reply. Looks like this seemed to be it! I saw that using ROUND fixed the issue, as well as going to File -> Options -> Advanced -> When calculating this workbook -> Set precision as displayed.

In my example, where everything below the threshold "works" and everything above it doesn't... well, I guess the precision is the underlying issue. I wish the "evaluate formula" would show me (by default) what it is actually evaluating. I'll try not to lose sleep over why that particular number is the threshold.

Thanks again!