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

On Jan 4, 5:02*pm, 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- Hide quoted text -

- Show quoted text -