VLOOKUP- Bizarre Formatting Issue

wiemsalot

New Member
Joined
Jul 5, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • VLOOKUP Question.PNG
    VLOOKUP Question.PNG
    51.9 KB · Views: 9

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

wiemsalot

New Member
Joined
Jul 5, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,907
Messages
5,544,984
Members
410,647
Latest member
LegenDSlayeR
Top