Using VLOOKUP with a Date and Time

[Guest]

I am having a problem using VLOOKUP when using a date and time. The Column "
Date TIME" has formatted cells in the form of MM/DD/YY HH:MM. In Column B (#
MSGS) I have a value in this case it is message counts.
I have a second Table consisting of "Date Time" Column also in the fomrat of
MM/DD/YY HH:MM. I want to match the "Date Time" Value in my Second Table
against the "Date Time" Value of my first table and extract the value of "#
Msgs" of this First table and store it in the MSGS column of the second table)

this is the formula I am using
=VLOOKUP(A1,TSAT_28JUL05_Activity!$B$5:$C$706,2,FALSE)

Basically A1 is the cell of my second table "Date Time" in this case and
TSAT_28JUL05_Activity!$B$5:$C$706 are the Date/Time and Msgs of my first
table. What am I doing wrong? I appreciate your help. Thank You!
First Table

Date Time # Msgs
7/21/05 0:03 1
7/21/05 0:05 15
7/21/05 0:06 3
7/21/05 0:07 1
7/21/05 0:10 3
7/21/05 0:11 6
7/21/05 0:15 5
7/21/05 0:16 5
7/21/05 0:20 5



Second Table

Date Time Count
7/21/05 0:01
7/21/05 0:02
7/21/05 0:03
7/21/05 0:04
7/21/05 0:05
7/21/05 0:06
7/21/05 0:07
7/21/05 0:08
7/21/05 0:09
7/21/05 0:10
7/21/05 0:11
7/21/05 0:12

 

[Roger Govier]

One table is formatted HH:MM:SS the other is only HH:MM hence the match will
not be found.
Get them both to the same format or use the TEXT(A1,"MM/DD/YY HH:MM) to only
look at the same formats.

 

[Guest]

Roger:
It worked thank you very much!

One table is formatted HH:MM:SS the other is only HH:MM hence the match will
not be found.
Get them both to the same format or use the TEXT(A1,"MM/DD/YY HH:MM) to only
look at the same formats.

 

[Galamdring]

Thank god for this forum...

I was having the same problems and the solution presented above worke
wonderfully.

But i have a question...

I am making a table that will then be copy-pasted into another exce
worksheet. Is there a way to remove those annoying #N/D error mark
whenever VLOOKUP doesn't find a value?

PS: In the english version the error may not be called #N/D... I a
using a Portuguese version (VLOOKUP = PROCV)

Thanks in advance... :

 

[Dave Peterson]

In the USA version, I could modify my formula:
=if(iserror(vlookup(...)),"",vlookup(...))

Another option may be to convert your formulas to values and then use the
equivalent of Edit|Replace to change those #n/d's to nothing.