Look up for a range, Possible?

T

t43m4n

Code:
--------------------

300-YARD SHUTTLE
≤ 45.9 10
46-47.9 9
48-49.9 8
50-51.9 7
52-53.9 6
54-55.9 5
56-57.9 4
58-59.9 3
60-61.9 2
≥ 62.0 1
 
M

Max

Just convert the table_array to use the lower bounds,
eg make it in say, A2:B11 as:

0 10
46 9
48 8
50 7
52 6
54 5
56 4
58 3
60 2
62 1

Then with lookup values (eg: 46.1, etc) listed in say, K2 down,
we could use in say, J2:
=IF(K2="","",VLOOKUP(K2,$A$2:$B$11,2))
and copy J2 down to return correspondingly

Alternatively, in this instance, since the table_array isn't too long, we
could also "hardcode" it as a stand-alone table_array and use instead in J2
=IF(K2="","",VLOOKUP(K2,{0,10;46,9;48,8;50,7;52,6;54,5;56,4;58,3;60,2;62,1},2))
The advantage is that we could use the above formula as-is in any sheet, by
just adjusting the lookup cell K2 to suit.
 
F

Flintstone

Yes, except the way you entered your numbers; Excel sees them as text
not numbers, just enter the first set of numbers with out the equals
(=) sign.
If you still need the look, custom format Col. B, rows two through
eight to change the appearance, (Format/Cells/Number Tab/Custom).
Example for cell B2: General"-47.9"

Col. A Col. B Col. C

46.1 45.9 10
46 9
48 8
50 7
52 6
54 5
56 4
58 3
60 2
62 1

=VLOOKUP(A1,B1:C10,2)


Matt
 
Top