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,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

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" <[email protected]> wrote in message
news:[email protected]...
>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:
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" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
> >
> >
>
>
>

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...

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