Look up for a range, Possible?

[t43m4n]

Please Login or Register  to view this content.

Is it possible to use vlookup for these? Like if the value is 46.1, it will return 9.

[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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"t43m4n" wrote:
>
> 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
>
> --------------------
>
>
> Is it possible to use vlookup for these? Like if the value is 46.1, it
> will return 9.
>
>
> --
> t43m4n
> ------------------------------------------------------------------------
> t43m4n's Profile: http://www.excelforum.com/member.php...o&userid=35536
> View this thread: http://www.excelforum.com/showthread...hreadid=567273
>
>

[Max]

Oops, line:
> we could use in say, J2:

should read as:
> we could use in say, L2:

.. was driving on the wrong side of the road there <g>
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[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