Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Charles
 
Posts: n/a
Default 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



  #2   Report Post  
Roger Govier
 
Posts: n/a
Default

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





  #3   Report Post  
Charles
 
Posts: n/a
Default

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






  #4   Report Post  
Galamdring
 
Posts: n/a
Default


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

  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Imported Date & Time format with calcs. managed in excel from imrp Todd F. Excel Worksheet Functions 0 July 8th 05 09:03 PM
Automatically enter date and time but only update once. PM New Users to Excel 3 January 21st 05 08:47 AM
Help - Information with time and date PM Excel Discussion (Misc queries) 4 January 6th 05 09:25 AM
Combined date time cell to separate date & time components Mark Ada Excel Discussion (Misc queries) 1 December 2nd 04 03:48 AM
time formatting and time categorizing (vlookup or data validation) MarianneR Excel Worksheet Functions 4 November 18th 04 04:24 PM


All times are GMT +1. The time now is 01:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"