Terry Detrie
New Member
- Joined
- Jul 23, 2015
- Messages
- 4
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
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