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?
Bookmarks