Hi, I've got a MATCH function and it's being really temperamental with time values.



Its referencing a data with that from a list of times in 5 minute intervals, so for example I have a value:

15/05/2013 09:00:00 (number 41409.375) being looked for in a list of numbers calculated using =IFERROR(MATCH(D157,'Energy Data'!$D:$D,0),"") where D157 is the 09:00, and 'Energy Data', column D is the list of times.

It works fine half the time, for example the above comes with row 12792.

But, and I've had this problem before in excel, if I have the same function on 14/05/2013 08:35:00 (41408.35763888890000), and I've checked the list contains 41408.35763888890000 but the function sill returns a blank (N/A for the match).

The thing is, even if I do a =D156='Energy Data'!C12499, it returns TRUE, so excel recognises they are equal!

If I copy and paste one cell value into the other, it manages to get them with the MATCH, so it thinks they are different. Somehow.

Has anyone come across this before? Any work around?

Perhaps post a small sample sheet containing the dubious examples?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hanvyj, my first thought was of times I've had a problem like this, where I'm doing MATCH or VLOOKUP on values that appear numeric, but in either the source or the target worksheet they're actually character strings. But you say you did <source>=<target> and got True back, so that's not the problem here.

It also occurred to me that you're searching for 09:00 (0.375) when the actual value is <date> 09:00 (n.375). Again, that doesn't seem like it can be the problem here.

A rounding error? Nah, shouldn't be that.

I just copied a date value to three other cells and formatted them as General, yyyy-mm-dd and yyyy-mm-dd hh:mm; but both the equality test and the MATCH function agrees that they're all equal.

I dunno, I have to agree with Pecoflyer; I need to see it.