• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MATCH Struggling with 00:00:00 format values?

JungleJme

New Member
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
 
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
 
Hi JungleJme,


You may also try this:


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


Regards,

Faseeh
 
Back
Top