Vlookup assistance
Posted by Tonya on January 23, 2002 7:35 AM
I am trying to put together a vlookup function that will go down the first column of the lookup table until it finds the first number larger than my value and then moves over to that column index column (instead of backing up one row).
Ex: actual bodyweight is 123.8
lookup table
col A col B
123.25 .4892
123.5 .4891
123.75 .4780
124.00 .4762 <- I want this value as the result
Can you tell me how to accomplish this? Do I need to provide more information? I appreciate the help. We are using this for running a high school powerlifting contest this weekend.
Thanks,
Tonya
Posted by Adam S. on January 23, 2002 8:52 AM
How's this?
It looks like your values in Column A increases incramentally by .25
How about this (where your lookup value is in C2):
=Vlookup(roundup(C2*4,0)/4,A:B,2,false)
The false forces excel to look for an exact match, so it's fairly dependant on your lookup range increasing by .25 all the way through.
Hope that helps out
Adam S.
Posted by Aladin Akyurek on January 23, 2002 8:52 AM
Assuming that A2:B5 houses your ssample data and D1 your lookup value, use:
=INDEX(B2:B5,MATCH(D1,A2:A5)+1)
This formula requires that your sample data is sorted on the first column.
===========
Posted by Aladin Akyurek on January 23, 2002 10:33 AM
In case an exact match is available and you'd want to have the corresponding value, otherwise the one that corresponds to the "larger lookup value", use:
=INDEX(B2:B5,IF(ISNUMBER(MATCH(D1,A2:A5,0)),MATCH(D1,A2:A5,0),MATCH(D1,A2:A5)+1))
instead of the shorter formula.
======================
