VLOOKUP Fails to find record

[Terry Detrie]

I have a VLOOKUP function that is failing to find its target.

Using the Evaluate Formula tool, here's the last few steps I get:

$N$46 = VLOOKUP(1.7, NoCirc, 5, 0)
$N$46 = VLOOKUP(1.7, $J$26:$N$45, 5, 0)
$N$46 = #N/A

(NoCirc is a dynamic range that always stops the row before current row).

In J43, there is a formula whose result is 1.7. The VLOOKUP function should find it.

If I edit formula in J43, then hit F9 to force a cell calculation, I get the expected 1.7, but all of a sudden the VLOOKUP function works.

Equally strange, if I instead copy and paste value for J43, the VLOOKUP function yields #N/A.

To explore this further I put the following formula in a different cell:
=J43=1.7

In all three cases (Formula, value via copy/paste, and value via cell calculation) this formula always says 'TRUE'. So why is it that the VLOOKUP doesn't work?

There's two more levels of strangeness going on:
1) that this is a single cell in a table with calculated columns (i.e., identical formula for everything in that column). None of the other formulas in that column are acting strangely. Same for the formulas in Column J.

2) the table is a receptacle for a data dump and the contents (of non calculated columns) is always changing. Cell N46 is usually working just fine, and this error can pop up in other locations of Column N.

Does anyone have a suggestion on how to force Excel to calculate this properly? Is there some obscure setting that needs to be changed?

Terry

 

[Trouttrap2]

Check to see if 1.7 is a number and not text. Check to see if 1.7 is truly 1.7 and not 1.695646464 or something like that. That's where i would start.

 

[Terry Detrie]

Thank you for the response.
I had already checked both possibilities. In any case, the formula =J43=1.7 would have yielded 'FALSE' result if it the problem was a number as text or a rounding error.

Your sig reminds me. I probably should state what version I'm using: Excel 2013.

 

[Terry Detrie]

The spreadsheet in question has been heavily used for the past six months, and has generally worked very well. Today however, I got call from my colleague to fix an error. Traced it down to the VLOOKUP problem mentioned above...and wouldn't you know it Excel was having a problem looking up the value 1.7 again.


As far as I know, this error has only come one other time in past six months. Most of the time Excel has no difficulty looking up the value 1.7. Is there anything that can explain such a strange intermittent error?

 

[Special-K99]

Usually this type of problem is solved quickly by examining the data to see if it actually does contain 1.7.
What does the VLOOKUP formula say?

Can you post the spreadsheet, remove any sensitive data.
You cant attach files on this forum.
Upload the file to an online storage site then place a link to it on this forum.

 

[Jonmo1]

It's probably related to Floating Point Precision Errors

What is the actual formula in J43?

You could wrap that formula in a round function
=ROUND(existingformula,2)

 

[Terry Detrie]

The values I'm referring to can have a lot of significant digits, but thankfully I have another formula that's tracking how many significant digits I should have.

=ROUND(existingformula,level)

This did work, thank you.

Terry

 

[Jonmo1]

You're welcome.