VLookUP Problem with Time Comparision

[DaveM]

I have an interesting problem. I have a series of time values to lookup in a
array but I am recieving a #NA reply on only a few even though the time
exists in the look up table. The majority of the values are returned
properly. For example the time values I wish to look up are listed in A1:A100
as

9:36
9:37
9:38
9:39
and so on for 100 cells.

In an adjacent cells B1:B100 i have the following lookup formula....

=VLOOKUP(TIME(HOUR(A1),MINUTE(A1),),'1MinData'!$D$2:E$7119,2,FALSE)

where it takes the hour and minute value of the A1 cell and goes to a
seperate sheet called 1MinData and looks down a list of times that look like
this...

9:35
9:35
9:36
9:36
9:36
9:37
9:37
and so on. If it finds the first match it will return the value 2 columns
to the right of the lookup array.

So most of the times return a proper value but in only a few cases if the
time 9:37 is the lookup value and the time 9:37 does show up in the lookup
array I get a #NA back. I have coverted the times to "general" format in
both the lookup value and the lookup table and I see that in these few cases
even though the time shows as 9:37 the decimal values are not exactly alike,
such as

0.400694444444444 (lookup array)
vs
0.400694444444446 (lookup value)

I've manually entered 9:37 into both locations to force the times to be
exact but the serial time value continues to show the differences in each
locations. Out of some 400 time lookups the NA returns on about 20 time
values with no real pattern.

Any thoughts/suggestions would be appreciated.

DaveM

on a few of the times while many of the others return values

 

[Dave Peterson]

Maybe an array formula would work better:

=INDEX('1mindata'!$E$2:$E$7119,
MATCH(TRUE,TEXT(A1,"hh:mm")=TEXT('1mindata'!$D$2:$D$7119,"hh:mm"),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

 

[Herbert Seidenberg]

Excel 2007
According to the attached test setup,
this occurs about 3.3% of the time.
A general solution might be to
MROUND to the nearest minute or second:
http://www.mediafire.com/file/zymzzv3yyol/12_28_08.xlsx

 

[Shane Devenshire]

Hi,

I'm not sure why you are using TIME(HOURS(A1),MINUTE(A1),)? Why not just
refer to A1

=VLOOKUP(A1,'1MinData'!$D$2:E$7119,2,)

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[DaveM]

Dave P;
Thanks the array formula you provided removed all the #n/a returns. Here's
my dilema now. I notice this formula requires apprx. 10 secounds or so to
calculate and the macro i run which updates (actally grows) the array by one
row per cycle updates every 5 seconds. So consequently the macro i run stops
while the spreadsheet cacluates then proceeds. This conflict results in the
macro only being able to cycle now about 3 times on average per minute.

Is there any thoughts on how we can achieve both? Your array forumla looks
like it converts the time to a text value then compares the two times as text
not time/date values as previous. Is there a way we can still do this
without an array formula? Say the same or similar formula i originally used
which calculated very quickly.

Thanks again and any thoughts would be appreciated.

Dave

 

[Pete_UK]

Presumably the data in your lookup table does not change.
Consequently, it might pay you to ensure that the values in column E
of 1MinData sheet are only hours and minutes by applying this formula
in a helper cell in that sheet:

=TIME(HOUR(D2),MINUTE(D2),)

and then copying down to row 7119. You can then fix these values, and
then copy/paste to overwrite the values in column E. You should then
be able to use your original formula.

Hope this helps.

Pete

 

[Dave Peterson]

Maybe you can convert the formulas to values after they've been populated --
well, if the values don't change after the initial evaluation????

 

[DaveM]

well if i understand you point the values that i retrieve from the lookup
does not change once i get it. I'm not sure how i'd replace the formula
though. Also, since i run the daily from 9:30 to 4:00 wouldn't i have to
replace the values with the formula once again to start the next day? Again,
if i'm understanding you correctly.

 

[DaveM]

the problem i have experieced trying this previously is that the look up area
on the 1MinData sheet (D2:E3000) approximately grows by one row each time the
macro runs. That is. the macro actually inserts a row (D3) which pushes all
previuos rows down the sheet. So at the begining of the day the lookup area
is really only a couple rows long but by the end of the day (4:00 pm ish) the
lookup area is now about 3000 rows long. When the insert happens on the
macro any formul like you noted to help is moved and the new row 3 is blank.
The macro then pastes some values (time in Col D and a different value in col
E). Not sure how your idea will work unless i can 'anchor' a formula or
reinsert a formula in the newly made row 3 after the insert row has occured.

How i haven't confused you too much.

DaveM

 

[Dave Peterson]

Yep.

You could do it manually by just copy|paste special|values all the ranges that
don't need to be changed.

You may need to keep multiple copies--one with the formulas and one with mostly
values and the rest formulas.

well if i understand you point the values that i retrieve from the lookup
does not change once i get it. I'm not sure how i'd replace the formula
though. Also, since i run the daily from 9:30 to 4:00 wouldn't i have to
replace the values with the formula once again to start the next day? Again,
if i'm understanding you correctly.

 

[Pete_UK]

In that case you need the macro to paste values into column D only in
hours and minutes - no seconds. This is what is causing you the
problems. Widen column D and adjust the format to hh:mm:ss to see what
you have at present.

Hope this helps.

Pete