D
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
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