Problem with "match" function

[Kwnstantinos_M]

Hi guys..

I have the following 3 columns in my excel sheet. The dates in column A (DateTime) are in the same format as column N(DateTime2).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DateTime (column A)[/TD]
[TD]DateTime2 (column N)[/TD]
[TD]Match[/TD]
[TD]VLOOKUP[/TD]
[/TR]
[TR]
[TD][TABLE="width: 115"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]1/1/16 0:00[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1/1/16 0:00[/TD]
[TD]1[/TD]
[TD]42370[/TD]
[/TR]
[TR]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="align: right"]1/1/16 3:00[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1/1/16 1:00[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]1/1/16 6:00[/TD]
[TD]1/1/16 2:00[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]1/1/16 9:00[/TD]
[TD]1/1/16 3:00[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]1/1/16 12:00[/TD]
[TD]1/1/16 4:00[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]1/1/16 15:00[/TD]
[TD]1/1/16 5:00[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]1/1/16 18:00[/TD]
[TD]1/1/16 6:00[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]1/1/16 21:00[/TD]
[TD]1/1/16 7:00[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]1/1/16 00:00[/TD]
[TD]1/1/16 8:00[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]

The data are a lot, so I can't check it one by one. I tried to use match command in order to identify which date from column N(DateTime2) exist in column A(DateTime). I used the following command:

=MATCH(N2;$A$2:$A$5849;0)

I used autofill and as a result I got only first value as a number (which means that it exists), and the rest as N/A ..which is wrong. As you can see the value 1/1/16 3:00 exist in both columns.

I tried then to use the function VLOOKUP and I wrote the following command:

=VLOOKUP(N2;$A$2:$A$5849;1;FALSE)

I got the same outcome.. I checked one random value (1/1/16 3:00) to check if the two cells with the same value are equal using the command : =A3=N5 and I got TRUE ..which means that the two cells are matching.

What do you think? What is the problem?

Thank you in advance
****** id="cke_pastebin" style="position: absolute; top: 216px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">Wh[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[mole999]

if you are trying to match the whole amount then the midnight one will be ok, the rest have decimal values for the part hours, so you won't match simply.

if you point this at A and then for N =ISNUMBER(cell) that has to return TRUE. The return then tells you what line that occurs on

 

[Kwnstantinos_M]

I'm not sure I understood that.. So you suggest to apply "=ISNUMBER(cell)" function to identify in which cells I have number?! And then what?

 

[mole999]

you need to be sure that all the cells return as a number otherwise they won't match, even when they look alike. the other way if the cells have no formatting, numbers line up on the right and text on the left