MATCH and time data - function not working

Hi, I’m learning to use the MATCH function (with the aim to master its combination with INDEX in the end), but as often happens when you’re new to something, I got stuck pretty much the moment I started:

All I’m trying to do at the moment, is to get the MATCH function to work with a database that contains time (see Column A), and a sample collection of certain time points (see Column B). The goal is for MATCH to return the corresponding element when it finds each of the ‘sample time’ points, and leave the cells blank when there’s no match to be found rather than showing an error message. To do this I’ve put in cells B18:B21 some formulas (which are listed in C18:C21 for easy referencing).

A B C
1 Database Sample
2 00:00:01 00:00:00
3 00:00:02 00:00:15
4 00:00:03 00:01:20
5 00:00:04 00:02:50
6 00:00:05 00:10:00
7 00:00:06 00:12:43
8 00:00:07 00:14:02
9 00:00:08 00:20:09
10 00:00:09 00:25:50
11 00:00:10 00:35:48
12 00:00:11 00:48:18
13 00:00:12 00:55:27
14 00:00:13
15 00:00:14
16 00:00:15
17 00:00:16 Result Formula
18 00:00:17 =IFERROR(MATCH(B2,$A$2:$A$30,0),"")
19 00:00:18 #N/A =MATCH(B3,$A$2:$A$30,0)
20 00:00:19 =IFERROR(MATCH(B4,$A$2:$A$30,0),"")
21 00:00:20 =IFERROR(MATCH(B5,$A$2:$A$30,0),"")
22 00:00:21
23 00:00:22
24 00:00:23
25 00:00:24
26 00:00:25
27 00:00:26
28 00:00:27
29 00:00:28
30 00:00:29

As you can see, it doesn’t work. Cell B19 is supposed to display 15 but just gives me the blank, and when I removed the ‘IFERROR’ part of the formula to try to figure out what went wrong, the cell then shows ‘#N/A’ which indicates that there’s no match available. This is clearly incorrect, so I tried to trouble shoot.

I could only assume it was the data itself that caused the problem, to test this I copied the data value from cell A16 into cell B3 and the formula in B19 starts working just fine, as if by magic. OK, so we’re getting closer to the bottom of this.

But that’s where my progress ends. When I manually entered the time data into cell B3, the formula in B19 wend back to displaying blank or #N/A, just like before.

It just doesn’t make any sense why Excel would do this. To me, a cell that says “00:00:15 AM” contains the same data as another cell that says “00:00:15 AM”, whether the time is manually entered or copied from another cell. I tried various formats for these cells just in case there’s some minor variance that wasn’t clearly shown, but nothing could be identified.

Now I’m truly confused and frustrated. Could anyone help please?

Sorry all, I tried to attached the problem in an Excel file but my computer freezes every time. So I tried to space the data in a way that shows the different columns clearly but somehow it all got pushed together... Guess I also need a lesson in Word editing (or is it forum posting?).

Originally Posted by xtong

Sorry all, I tried to attached the problem in an Excel file but my computer freezes every time.

Instead of using this forum file-attachment feature, upload an example Excel file to any file-sharing website and post the public/share URL in a response here. Some free websites:

Dropbox: http://dropbox.com
Box.Net: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com
Windows OneDrive: http://onedrive.live.com

Originally Posted by xtong

So I tried to space the data in a way that shows the different columns clearly but somehow it all got pushed together.

I think it is okay as-is. But to get things spaced the way you want, either use this forum's table feature or simply put CODE and /CODE tags around the text, format the text in Notepad or similar plain-text editor (not MS Word), and copy-and-paste the text between the CODE and /CODE tags.

But it might not be worth the trouble. The MATCH might fail because the numeric value of the lookup time does not exactly match the numeric value of the times in the lookup table.

That is why we need to have the example Excel file, not an image and not retyped text: the devil is in the details.

Thank you FDibbins, I think that's exactly why the formulas wouldn't work. Guess I picked a troublesome type of data to begin with, I usually deal with financial data and the best precision I've seen is 4 decimal places

Originally Posted by FDibbins

1 way around is to round (up or down) on both sets of values (your table and your match criteria) to something like 10 decimal places...play with the amount to give you te accuracy (or lack there-of) that you are comfortable with

IMHO, it is bad programming practice to round time arithmetic to an arbitrary number of decimal places. That just replaces one inaccurate binary representation with another. Even if we try to be consistent with "both sets of values", it might probably cause problems somewhere eventually. Moreover, it is unusual to round time constants, which is often one set of values.

The correct procedure is to round time arithmetic to the time precision that we require. For example, if time arithmetic should be accurate to one minute, round to one minute using one of the following paradigms:

--TEXT(A1-B1,"[h]:mm")
or
ROUND((A1-B1)*1440,0)/1440

The double-negate converts the text result to numeric time.

One advantage of using ROUND: we can easily opt to ROUNDDOWN or ROUNDUP, if we require. Moreover, "[h]:mm" does not work for all regions, by default.

On the other hand, we might expect the TEXT conversion to result in exactly the same binary representation as conversion of the equivalent constant. Theoretically, division by 1440 might result in a slightly different binary representation, differing by an infinitesimal amount. (But in practice, I believe it does not.)

Errata...

Originally Posted by joeu2004

ROUND((A1-B1)*1440,0)/1440
[....]
Theoretically, division by 1440 might result in a slightly different binary representation, differing by an infinitesimal amount. (But in practice, I believe it does not.)

Actually, that is incorrect. For example, if A1 is 23:58 and B1 is =ROUND(A1*1440,0)/1440, the formula =MATCH(A1,B1,0) and even =MATCH(A1,B1,1) return the #N/A error (no match) because B1 is infinitesimally greater than A1.

Originally Posted by FDibbins

joeu2004 thanks for the math lecture.
Perhaps read entire posts next time, i had already covered your dissertation...

1 way around is to round (up or down) on both sets of values (your table and your match criteria) to something like 10 decimal places...play with the amount to give you te accuracy (or lack there-of) that you are comfortable with

Not surprisingly, you entirely missed the point of my "lecture".

Selecting 10 decimal places or even "playing with the amount" (decimal places?) is not the appropriate way to round time. That just replaces one inaccurate binary representation with another.

You suggest doing that with "both sets of values" so that both sets are equally inaccurate. But IMHO, it is ludicrous to expect a user to enter ROUND("16:28",10) instead of simply 16:28.