Range Lookup

[Burntchips]

Hi All,

I'm trying to create a range lookup formula in excel and am having some problems. Let me describe the scenario.

Say I have the following grid

Level min max
1 0 10
2 10 32
3 32 50

.. (etc)

given an number (example 23) how would I write a formula that finds the "Level" for this number. exmaple: 23 should return 2 as 23 falls between the 10-32 range defined in the chart.

[martindwilson]

what about 10? or 32? what range would they fall in?

[Burntchips]

Very good point. Is it possible to just return the first matched range? I could also change the data so the next range starts at +1 of the last ranges end.

[martindwilson]

one way , but depends on your boundaries and really how large the look up would be is
=LOOKUP(A1,{0,11,33},{1,2,3})

[Blake 7]

Hi Martin - if you have the time/inclination, would you mind translating this formula for me please LOOKUP(A1,{0,11,33},{1,2,3}) in particular, the curly brackets. Thanks v much
Darren

[martindwilson]

if it was say
B1=0
B2=11
B3=33
AND
D1=1
D2=2
D3=3
you'd use
LOOKUP(A1,b1:b3,d1:d3)
but since you aren't using a cell range you can create your own using { }
lookup returns from excel help

If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

[Blake 7]

Thanks Martin - for your time. Really helpful.

So if i was to go away and study this function should o be googling "lookup Vector" or "Lookup Array" or just plain Lookup?

Thanks mate