VLOOKUP Round Down instead of Round Up?

Attached is a US Army 2 mile run standard scoring chart. The VLOOKUP function works well, except that it rounds up returned times and points, rather than rounding down.

For example. a time of 14:18 for a 42-26 year old male returns 98 points, which is correct.
A time of 14:12 for a 42-26 year old male returns 99 points, which is correct.
A time of 14:17, however, for a 42-46 year old male retuns 99 points, which is incorrect ... the runner should receive 98 points until achieving the 14:12 time threshold.

Hope I've explained this well enough. Thanks in advance.
VLOOKUP-BASIC-TIMES.xls
Cactus1993OwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RichardSchollarCommented:
Hi

That happens because of the way VLOOKUP works - if it can't find an exact match, it matches against the next lowest value in the lookup range (assuming an ordered list).  In the attached, I have shown one way around this (which doesn't require changing the layout of your data).  I have also shown how you can specify which column to take the data from if you provide a second lookup field of the age range (please refer to E1:G5.

Formula I have used using your orginal layout is:

=INDEX($B$8:$I$86,MATCH(A2,$A$8:$A$86,1)+(LOOKUP(F2,$A$8:$A$86)<>F2),6)

which you would place in B2 and copy down.

Richard


VLOOKUP-BASIC-TIMES.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
answer_dudeCommented:
You can use the ROUNDDOWN function...

=ROUNDDOWN(vlookup(...),0)
Cactus1993OwnerAuthor Commented:
Thanks! This solution works perfectly.

(Answer_Dude: The rounddown suggestion with the VLOOKUP / INDEX-MATCH function I needed to use didn't work.)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.