Find unique value in list (TIME), not working

[Mikael Lindqvist]

Good morning everyone,

I'm fairly good with Excel but this problem really stumps me. I have one
list with two columns:

1) Time (hh:mm:ss)
2) Integer (interval 0 to 200)

Now, from a value (reference next cell on the left) I want to match the
"Time" in my list (above) and return the integer on same row.

A simple FINDROW (I think that's the english name for the function, I'm
using a swedish version) does the trick, all the time, but not now. If I
force an exact match I get the "#missing!"

Same thing for the super-allround index(c1:c10;match(a1;d1:d10;0)

If I set it to not exact matching it "guess" right most of the time, but not
always.

This surely has something to do with the "time" format of the cell, but I
can't for my life guess what's causing this hicup.

Here's an example of the data:

LIST, kolumn C and D
Integer, Time
0 00:01:00
0 00:02:00
0 00:03:00
1 00:04:00
1 00:05:00
1 00:06:00
2 00:07:00

Kindly,
Mikael
Sweden

 

[Bernd P]

Hello Michael,

Sounds like a rounding issue to me. Round your time values as well as
your search value to the second or sort them and match"guess" your
searchvalue, I suggest.

Change the time format of your time values and your search to General
for a moment and look how precise they are given...

Regards,
Bernd

 

[Herbert Seidenberg]

I agree with everything Bernd said.
To round a time to the nearest second
you can use this formula:
=MROUND(MyTime,1/24/60/60)