software

We have discovered a problem in referencing time using the LOOKUP
function and I haven't been able to resolve it using VLOOKUP or the
INDEX and MATCH combination

The easiest way to demonstrate the problem is to creat a list of times
from 10:00 am to 12:00 pm in 15 minute increments, then copy the list
into the adjacent column and convert that list into the decimal
equivalent. See below for what the list should look like. I expanded
the decimals to the maximum number of decimals.

10:000.416666666666667
10:150.427083333333333
10:300.437500000000000
10:450.447916666666667
11:000.458333333333333
11:150.468750000000000
11:300.479166666666667
11:450.489583333333333
12:000.500000000000000

Now if you write a formula using LOOKUP or VLOOKUP to try to return the
decimal equivalent of a time in the left column. It works fine except
at a few times like 10:45 and 11:30 when it returns the values for
10:30 and 11:15 respectively.

Any help would be appreciated.

Jeff--
jjhmbh
------------------------------------------------------------------------
jjhmbh's Profile: www.excelforum.com/member.php...oamp;userid=29748
View this thread: www.excelforum.com/showthread...hreadid=494626Both LOOKUP and VLOOKUP work okay for me in my (maybe limited) tests.

What data are you comparing, a time string or time value, and what formula
are you using.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;jjhmbhquot; gt; wrote in
message ...
gt;
gt; We have discovered a problem in referencing time using the LOOKUP
gt; function and I haven't been able to resolve it using VLOOKUP or the
gt; INDEX and MATCH combination
gt;
gt; The easiest way to demonstrate the problem is to creat a list of times
gt; from 10:00 am to 12:00 pm in 15 minute increments, then copy the list
gt; into the adjacent column and convert that list into the decimal
gt; equivalent. See below for what the list should look like. I expanded
gt; the decimals to the maximum number of decimals.
gt;
gt; 10:00 0.416666666666667
gt; 10:15 0.427083333333333
gt; 10:30 0.437500000000000
gt; 10:45 0.447916666666667
gt; 11:00 0.458333333333333
gt; 11:15 0.468750000000000
gt; 11:30 0.479166666666667
gt; 11:45 0.489583333333333
gt; 12:00 0.500000000000000
gt;
gt; Now if you write a formula using LOOKUP or VLOOKUP to try to return the
gt; decimal equivalent of a time in the left column. It works fine except
gt; at a few times like 10:45 and 11:30 when it returns the values for
gt; 10:30 and 11:15 respectively.
gt;
gt; Any help would be appreciated.
gt;
gt; Jeff
gt;
gt;
gt; --
gt; jjhmbh
gt; ------------------------------------------------------------------------
gt; jjhmbh's Profile:
www.excelforum.com/member.php...oamp;userid=29748
gt; View this thread: www.excelforum.com/showthread...hreadid=494626
gt;

I am using LOOKUP and use the time value.

Also I am referencing the time outside of the array. If I reference
the time in the array, it returns the correct value. For instance, if
you use the array below

lt;Agt; lt;Bgt; lt;Cgt;
11:001 11:30
11:152
11:303
11:454
12:005

The formula LOOKUP(A3, A1:B5) returns 3
But if you reference the value outside of the table, say in cell C1,
the formula returns 2.--
jjhmbh
------------------------------------------------------------------------
jjhmbh's Profile: www.excelforum.com/member.php...oamp;userid=29748
View this thread: www.excelforum.com/showthread...hreadid=494626It's the rounding at all those decimal places that is causing the
issue...try using the ROUND function on you lookup times, say to 6 decimals
and then wrap the looked up value in a ROUND function too using the same
decimals,like

=VLOOKUP(ROUND(A1,6),Sheet1!$A$1:$B$24,2,FALSE)

Using a little trial and error you will only be losing precision in seconds
or probably milliseconds.

One way at least if that precision is not necessary

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HISquot;jjhmbhquot; gt; wrote in
message ...
gt;
gt; We have discovered a problem in referencing time using the LOOKUP
gt; function and I haven't been able to resolve it using VLOOKUP or the
gt; INDEX and MATCH combination
gt;
gt; The easiest way to demonstrate the problem is to creat a list of times
gt; from 10:00 am to 12:00 pm in 15 minute increments, then copy the list
gt; into the adjacent column and convert that list into the decimal
gt; equivalent. See below for what the list should look like. I expanded
gt; the decimals to the maximum number of decimals.
gt;
gt; 10:00 0.416666666666667
gt; 10:15 0.427083333333333
gt; 10:30 0.437500000000000
gt; 10:45 0.447916666666667
gt; 11:00 0.458333333333333
gt; 11:15 0.468750000000000
gt; 11:30 0.479166666666667
gt; 11:45 0.489583333333333
gt; 12:00 0.500000000000000
gt;
gt; Now if you write a formula using LOOKUP or VLOOKUP to try to return the
gt; decimal equivalent of a time in the left column. It works fine except
gt; at a few times like 10:45 and 11:30 when it returns the values for
gt; 10:30 and 11:15 respectively.
gt;
gt; Any help would be appreciated.
gt;
gt; Jeff
gt;
gt;
gt; --
gt; jjhmbh
gt; ------------------------------------------------------------------------
gt; jjhmbh's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29748
gt; View this thread: www.excelforum.com/showthread...hreadid=494626
gt;
I think you are entering something incorrectly, as it returns 3 for me.

Is A1:A5 typed in, or a formula result? I can't get 2 no matter what I have
tried.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;jjhmbhquot; gt; wrote in
message ...
gt;
gt; I am using LOOKUP and use the time value.
gt;
gt; Also I am referencing the time outside of the array. If I reference
gt; the time in the array, it returns the correct value. For instance, if
gt; you use the array below
gt;
gt; lt;Agt; lt;Bgt; lt;Cgt;
gt; 11:00 1 11:30
gt; 11:15 2
gt; 11:30 3
gt; 11:45 4
gt; 12:00 5
gt;
gt; The formula LOOKUP(A3, A1:B5) returns 3
gt; But if you reference the value outside of the table, say in cell C1,
gt; the formula returns 2.
gt;
gt;
gt; --
gt; jjhmbh
gt; ------------------------------------------------------------------------
gt; jjhmbh's Profile:
www.excelforum.com/member.php...oamp;userid=29748
gt; View this thread: www.excelforum.com/showthread...hreadid=494626
gt;