One more thought:
The data isn't doing what I want it to. Here's what I've got on my worksheet
on which I'm looking up scores: 1. A column with a list (from fastest to
slowest) of run times (ie. ascending slower and slower); 2. A column (to the
right) with corresponding points from 100 (fastest) to 45 (slowest) in
increments of 5 points (see below for example):
Row'G' Row'H'
9:20 100
9:45 95
10:00 90
10:30 85
11:00 80
11:15 75
12:00 70
13:00 65
13:45 60
14:00 55
14:15 50
14:30 45
The correct points for the time "12:46" is 65, but my formula currently
makes the result 70.
Here's my formula (on the master page): =VLOOKUP(U4,'M4'!G2:H13,2)
Appreciate your thoughts.
NavyPianoMan

"David Biddulph" wrote:

Having read your message for a second time, I'm intrigued. I'm interested
to how 13:15:00 can be AM. I would have expected 13:15:00 or 1:15:00 PM.
What format do you see in the format box if you select "custom"? ... and
what value do you see in the cell if you format to General or Number?
--
David Biddulph

"NavyPianoMan" wrote in message
...
I need to format a cell as elapsed time for a run time. For example, I
want
to use "13:15" to designate "13 minutes and 15 seconds" and use it in an
ascending array (VLOOKUP). Right now, Excel makes it into "13:15:00 AM",
which doesn't help me.

If an Excel function doesn't give the answer you are expecting, I would
recommend looking up the formula in Excel help and seeing what it says.

In your case you've omitted the TRUE/ FALSE for the range_lookup parameter
for the VLOOKUP function, and help tells you:
"If TRUE or omitted, an approximate match is returned. In other words, if an
exact match is not found, the next largest value that is less than
lookup_value is returned."

Isn't that what it has done?
--
David Biddulph

"NavyPianoMan" wrote in message
...
One more thought:
The data isn't doing what I want it to. Here's what I've got on my
worksheet
on which I'm looking up scores: 1. A column with a list (from fastest to
slowest) of run times (ie. ascending slower and slower); 2. A column (to
the
right) with corresponding points from 100 (fastest) to 45 (slowest) in
increments of 5 points (see below for example):
Row'G' Row'H'
9:20 100
9:45 95
10:00 90
10:30 85
11:00 80
11:15 75
12:00 70
13:00 65
13:45 60
14:00 55
14:15 50
14:30 45
The correct points for the time "12:46" is 65, but my formula currently
makes the result 70.
Here's my formula (on the master page): =VLOOKUP(U4,'M4'!G2:H13,2)
Appreciate your thoughts.
NavyPianoMan

I understand how the range_lookup parameter works (I'd researched this), but
in my case, I can't use "FALSE" because it will just produce a #N/A and
"TRUE" or omission produces a mistaken result due to chosing the NEXT
approximate match. I actually need to chose the LAST approximate match. How
can I do this?
Here's my example again (this is from the worksheet 'M4'!):
Row'G' Row'H'
9:20 100
9:45 95
10:00 90
10:30 85
11:00 80
11:15 75
12:00 70
13:00 65
13:45 60
14:0 55
14:15 50
14:30 45
The correct points for the time "12:46" (U4) is 65, but my formula currently
makes the result 70.
Here's my formula (on the master page): =VLOOKUP(U4,'M4'!G2:H13,2)
Thanks,
NavyPianoMan


"David Biddulph" wrote:

If an Excel function doesn't give the answer you are expecting, I would
recommend looking up the formula in Excel help and seeing what it says.

In your case you've omitted the TRUE/ FALSE for the range_lookup parameter
for the VLOOKUP function, and help tells you:
"If TRUE or omitted, an approximate match is returned. In other words, if an
exact match is not found, the next largest value that is less than
lookup_value is returned."

Isn't that what it has done?
--
David Biddulph

"NavyPianoMan" wrote in message
...
One more thought:
The data isn't doing what I want it to. Here's what I've got on my
worksheet
on which I'm looking up scores: 1. A column with a list (from fastest to
slowest) of run times (ie. ascending slower and slower); 2. A column (to
the
right) with corresponding points from 100 (fastest) to 45 (slowest) in
increments of 5 points (see below for example):
Row'G' Row'H'
9:20 100
9:45 95
10:00 90
10:30 85
11:00 80
11:15 75
12:00 70
13:00 65
13:45 60
14:00 55
14:15 50
14:30 45
The correct points for the time "12:46" is 65, but my formula currently
makes the result 70.
Here's my formula (on the master page): =VLOOKUP(U4,'M4'!G2:H13,2)
Appreciate your thoughts.
NavyPianoMan