Using VLOOKUP with a Date and Time
 

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,FA LSE)

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

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.

--
Regards

Roger Govier


"Charles" wrote in message
...
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,FA LSE)

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:
It worked thank you very much!

"Roger Govier" wrote:

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.

--
Regards

Roger Govier


"Charles" wrote in message
...
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,FA LSE)

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

Thank god for this forum... :)

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

But i have a question...

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

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

Thanks in advance... :)


--
Galamdring
------------------------------------------------------------------------
Galamdring's Profile: http://www.excelforum.com/member.php...o&userid=25459
View this thread: http://www.excelforum.com/showthread...hreadid=391479

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.



Galamdring wrote:

Thank god for this forum... :)

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

But i have a question...

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

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

Thanks in advance... :)

--
Galamdring
------------------------------------------------------------------------
Galamdring's Profile: http://www.excelforum.com/member.php...o&userid=25459
View this thread: http://www.excelforum.com/showthread...hreadid=391479

--

Dave Peterson