vertical look up problem with date and time

  • Thread starter Thread starter btokgoz
  • Start date Start date
B

btokgoz

hi,

I'm trying to compare intraday stock volume data for 2 stocks. The
stocks do no trade every minute.
I have made a new table that consists every minute of the day. With the
vertical look-up function I try to get the volume quotes in my new
table for every minute that a trade has occurred.
So, since not every minute the stocks trade I expect to see a zero or
(N/B) in my new table. But instead of doing this my new table shows a
volume from the trade that has happened 1 or 2 minutes ago.

How can I overcome this problem?? (I've attached an example xls with
some comments)

Thanks, appreciate it!

PS (does anyone now how to get a 0 (zero) in a cell, whenever #N/B is
shown in that cell)

Attachment filename: example.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=422727
 
Hi
for your first problem:
Simply add the 4th parameter of the VLOOKUP function. With this you can
indicate that you search for exact matches. E.g. change your vormula in
cell I4 to
=VLOOKUP(G4,$A$4:$B$12,2,FALSE)

For the second one use
=IF(ISNA(VLOOKUP(G4,$A$4:$B$12,2,FALSE)),0,VLOOKUP(G4,$A$4:$B$12,2,FALS
E))

HTH
Frank
 
Hi
You have a 3rd problem:
your lookup value (37988.6506940394) is not equal to the one in th
table (37988.6506944444). You'll have to do some time-calculation

avne
 
Thanks for the help. I succeed in doing it. Coverted the decimals to
behind the dot and it worked
 
Back
Top