MrExcel Publishing
Your One Stop for Excel Tips & Solutions

time measures and vlookup

Posted by brian o'farrell on December 14, 2000 2:34 PM

I am developing a spreadsheet for an athletics club that enables them to easily determine the points relevant to a particular performance. The times are measured to 100ths of a second and may involve minutes (or even hours for the adult events). I have arranged the data in two columns - TIMES and POINTS - and proposed to use a vlookup based upon TIME. The times are entered as format e.g. 2:40.35. Is there an easy way to achieve this without converting the data to seconds for the lookup?
Melbourne, Australia

Posted by Tim Francis-Wright on December 14, 2000 9:12 PM

You should be able to do this; here's a toy example:

A1: 0:00.00
A2: 0:30.00
A3: 0:35.00
A4: 0:42.00
A5: 59:59.99

B1: 100
B2: 80
B3: 60
B4: 0
B5: 0

(In each case, Bn is the number of points
for not going over A[n+1] in terms of time.)

As long as you format the cells correctly,
excel will treat each one as a number. To do
the VLOOKUP without requiring exact matches,
Excel needs the times in ascending order.

So, if C1 = 0:38, then
C2 = Vlookup(C1,$a$5:$b$5,2,TRUE) = 60

Hope this helps!