# VLOOKUP- Bizarre Formatting Issue

#### wiemsalot

##### New Member
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
51.9 KB · Views: 10

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### wiemsalot

##### New Member

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!

Replies
3
Views
118
Replies
0
Views
65
Replies
2
Views
70
Replies
6
Views
198
Replies
8
Views
1K