Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a column:

C1: 0:8:35 
C2: 0:8:40 
C3: 0:8:45 
and so on.

D1: 0:8:40

I'm trying this :

VLOOKUP(C2,C1..C30,1,TRUE) returns: 0:8:40

(1 - we have one column) (TRUE - range lookup - should give the closest number in ascending order !

When I try VLOOKUP(D1,c1..c30,1,TRUE) returns 0:8:35

Altough C2 and D1 have the exact same value (if(c2=d1, "ok", "not ok") returns OK.

I am using range lookup so I can find also 0:8:36 ect...

Any ideas ?

IMPORTANT EDIT: it works ok when you enter the numbers in the search column. if you create the list using 0:8:35 and each next cell as the previous + 0:0:05 - than the error occurs !! checked the evaluation of the calculation and in both case it's evaluates to the same number, but yields different results !

share|improve this question
up vote 1 down vote accepted

using Trunc(c1,16) fix it..... although they are exactly the same only after trunc it works.

share|improve this answer

Always use false instead of true.

False gets you the exact match.
True gets you similar matches.

share|improve this answer
In this case I needed a range match and not exact match. – Dani Nov 20 '10 at 6:06

Try copying the 'format' of c1 into D1 and see if that helps.

share|improve this answer
it didn't help. it is a bug, but I've found a solution. – Dani Nov 16 '10 at 19:29

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.