Forum Discussion

teron nguyen's avatar
teron nguyen
Copper Contributor
Jun 09, 2017

Match function in excel is only working partly

I am using Match function in excel to match individual time value with a time array, however the function is only working partly, meaning for several first data is correct but the rest ones are not working. It is quite strange.
Both data format are the same, if not it will not working at the beginning.

The Match function as a component of IFERROR(INDEX(MATCH))) and after checking I see the problem comes from here.

This image: https://i.stack.imgur.com/pcdhe.jpg shows Match function from value in B column to C column: 

MATCH($B133,$C$6:$C$16,0)

This image: https://i.stack.imgur.com/tgXDS.jpg

shows no difference in 16 decimal digit of time value

 

Column B:

1/6/2017 15:02:44
1/6/2017 15:03:14
1/6/2017 15:03:44
1/6/2017 15:04:14
1/6/2017 15:04:44
1/6/2017 15:05:14
1/6/2017 15:05:44
1/6/2017 15:06:14
1/6/2017 15:06:44
1/6/2017 15:07:14
1/6/2017 15:07:44

Column C:

1/6/2017 15:02:37
1/6/2017 15:02:38
1/6/2017 15:02:39
1/6/2017 15:02:40
1/6/2017 15:02:41
1/6/2017 15:02:42
1/6/2017 15:02:43
1/6/2017 15:02:44
1/6/2017 15:02:45
1/6/2017 15:02:46
1/6/2017 15:02:47
1/6/2017 15:02:48
1/6/2017 15:03:13
1/6/2017 15:03:14
1/6/2017 15:03:15
1/6/2017 15:03:38
1/6/2017 15:03:42
1/6/2017 15:03:43
1/6/2017 15:03:44
1/6/2017 15:03:45
1/6/2017 15:04:12
1/6/2017 15:04:13
1/6/2017 15:04:14
1/6/2017 15:04:15
1/6/2017 15:04:42
1/6/2017 15:04:43
1/6/2017 15:04:44
1/6/2017 15:04:45
1/6/2017 15:05:12
1/6/2017 15:05:13
1/6/2017 15:05:14
1/6/2017 15:05:15
1/6/2017 15:05:43
1/6/2017 15:05:44
1/6/2017 15:05:45
1/6/2017 15:06:13
1/6/2017 15:06:14
1/6/2017 15:06:15
1/6/2017 15:06:40
1/6/2017 15:06:41
1/6/2017 15:06:42
1/6/2017 15:06:43
1/6/2017 15:06:44

Pls give me a help, thank you !




Formulas & Functions
Need Help
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jun 09, 2017

    Hello

     

    Time values may look identical but being decimal numbers they always bear the problem of a floating-point error.

     

    This is one possible workaround:

     

    {=MATCH(INT(B133)+TIME(HOUR(B133),MINUTE(B133),SECOND(B133)),INT($C$6:$C$16)+TIME(HOUR($C$6:$C$16),MINUTE($C$6:$C$16),SECOND($C$6:$C$16)),0)}

     

Resources