View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
NavyPianoMan NavyPianoMan is offline
external usenet poster
 
Posts: 25
Default VLOOKUP won't generate correct response for "inbetween" values

In the following example, the time "13:15" generates the points "65". I want
it to generate the points "60". The formula seems to be connecting the
lookup-value S2 (13:15) with the 65 row. I need it to chose the 60 row,
because the time 13:15 is not fast enough to belong to the "13:00" group, but
fits within the range of "13:01 to 13:45".
How can I change the formula so I get the result of "60" points?

My formula: =VLOOKUP(S2,'M4'!G2:H13,2)
"S2"=13:15 (formatted as [mm]:ss)
'M4'!=the worksheet with the following relevant columns:
Col'G' Col'H'
Run Points
09:20 100
09: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