Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

3 Answers

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
add comment

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
add comment

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
add comment

Your Answer

 
discard

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.