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.

"Dave Peterson" wrote:

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 wrote:

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

"Dave Peterson" wrote:

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.

DaveM wrote:

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


--

Dave Peterson