MATCH Struggling with 00:00:00 format values?

[JungleJme]

Hey chandooistas,


I am no guru, but i'm decent enough with excel and this has me stumped.


I have a list of times starting at 00:00:00 and increase in ten second increments. In the next column is a value.


In another table i want to match against various times and return the corresponding value.


However with match type set to zero, i get n/a


With match type set to 1 i get values but mostly one row off (too low). I can't do plus one as a workaround because sometimes the match formula gets the right row but only manual checking shows when it does and doesn't get it right...


If i do an = on the two values that aren't matching i get a TRUE result.


What's up with MATCH?! I thought it was all powerful but am no disillusioned....


Sample workbook here - http://dropcanvas.com/eii8l

 

[NARAYANK991]

Hi ,


Use the ROUND function as follows :


=INDEX($A:$B,MATCH(ROUND($H3,5),ROUND($A:$A,5),0),2)


Enter this as an array formula , using CTRL SHIFT ENTER.


Narayan

 

[Faseeh]

Hi JungleJme,


You may also try this:


=LOOKUP(TEXT($H3,"@"),TEXT($A$3:$A$183,"@"),$B$3:$B$183)


Regards,

Faseeh