vlookup with scroll bar not quite working

Hi everybody!
In the attached file i'm trying to have a scrolling list. the problem is that the vlookup is returning #N/A if the scroll value is not divisble by 3. The same lookup value returns a result at different increment values. I can resolve this by having the scroll increment in units of 3 but i would like to know what the problem is.

Use this in F4, copied down:

=$A$4+ROWS($F$4:F4)*$F$2*15/24/60

In addition there's something very strange about your vlookup. It is saying: Look up this thing. If the answer is zero, put N/A, otherwise look it up again.

Easier to use:

=IFERROR(VLOOKUP(F4,$A$4:$D$1769,2,FALSE),"N/A")

I's advise against using #N/A, as that has a specific meaning in Excel and is a useful diagnostic if/when things go wrong.

@Glenn: maybe 0 is a valid return that the OP needs to replace by N/A. Probably still needs the IFERROR wrapper.

Fair point, Trevor... In which case, use:

=IFERROR(1/(1/VLOOKUP(F4,$A$4:$D$1769,2,FALSE)),"N/A")

PS. I do like the idea of the scroll bar "not quite" working....like a tyre only being flat at the bottom, or being a little bit pregnant....

To be fair, tyres rarely go flat at the top

OK. Apols. I must have been asleep when i replied. Your problem is caused by Excel's (in)ability to calculate time (and other numbers) correctly. The system they use is called "Floating Point Arithmetic" and it can cause all sorts of fun. So... although if you do =DATETIME1 = DATETIME2 on two cells, corresponding to a #N/A error, you'll get TRUE, in reality they aren't the same... Confused?? It gets worse. To fix your problem you have to modify the VLOOKUP. Take the lookup date time, multiply it by 1440, round to zero decimal places and divide it by 1440. Bingo. Problem gone....

So, G4 now looks like this:

=IFERROR(VLOOKUP(ROUND(1440*F4,0)/1440,$A$4:$D$1769,2,FALSE),NA())

I take your point about #N/A. I'm 99.9% certain that you have to create it using the NA() function. If you just create text that looks like #N/A, your graph may NOT behave as expected. I haven't checked that, however.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.