Excel - Lookup against a range

[Martynt74]

Hi

I have a range of data in 3 columns such as

Column 1 Column 2 Column 3
1..................2...............Low
3..................6...............Medium
7.................12..............High
13................20..............Freaky High

Etc, the range between column 1 and 2, isn't always consistant as above, it then has a description in column 3

I then have another table containing data, and i'd like to have a lookup which will look at the above table and return column 3, depending on where the number lies.

So if i enter 4 in the other table it will pull back "Medium", if i enter 14 it will return "Freaky High"

I initially considered nested IFs, however the table containing values is about 80 rows long and that starts to get pretty messy!

Does anyone have any good ideas how i could achieve this?

Cheers

 

[Martynt74]

Unfortunately not, its on behalf of my dad and it seems that at the extremes the bands are quite wide and the middle ones are much narrower.

 

[Martynt74]

No, sometimes they can be as high as 30 between each band and other times the difference can be as small as 0.2.

Edit: I think i've sorted it, i've ignored column 2and done a vlookup against column 1 only but used the TRUE function at the end rather than false which seems to only look between the values in each row.

I've never really used TRUE in a lookup before but this seems to be working quite well.

 

[Martynt74]

Hence my question

Sorry, my example data wasn't great, the actual data range was the following which would've made the table quite tricky

Low Act K Upp Act K Act SL
-10.00 0.01 50.00
0.02 0.03 51.00
0.04 0.05 52.00
0.06 0.08 53.00
0.09 0.11 54.00
0.12 0.13 55.00
0.14 0.16 56.00
0.17 0.19 57.00
0.20 0.21 58.00
0.22 0.93 59.00
0.24 0.26 60.00
0.27 0.28 61.00
0.29 0.31 62.00
0.32 0.34 63.00
0.35 0.36 64.00
0.37 0.39 65.00
0.40 0.45 66.00
0.46 0.47 68.00
0.48 0.50 69.00
0.51 0.53 70.00
0.54 0.57 71.00
0.58 0.59 72.00
0.60 0.62 73.00
0.63 0.65 74.00
0.66 0.68 75.00
0.69 0.71 76.00
0.72 0.74 77.00
0.75 0.78 78.00
0.79 0.81 79.00
0.82 0.85 80.00
0.86 0.89 81.00
0.90 0.93 82.00
0.94 0.96 83.00
0.97 1.01 84.00
1.02 1.05 85.00
1.06 1.09 86.00
1.10 1.15 87.00
1.16 1.19 88.00
1.20 1.25 89.00
1.26 1.30 90.00
1.30 1.37 91.00
1.38 1.43 92.00
1.44 1.50 93.00
1.51 1.59 94.00
1.60 1.69 95.00
1.70 1.79 96.00
1.80 1.97 97.00
1.98 2.25 98.00
2.26 2.33 99.00
2.34 2.36 99.10
2.37 2.40 99.20
2.41 2.45 99.30
2.46 2.50 99.40
2.51 2.60 99.50
2.61 2.69 99.60
2.70 2.81 99.70
2.82 2.87 99.80
2.88 3.12 99.90
3.12 10.00 100.00

 

[Martynt74]

Ah very well spotted. I'll have another play tomorrow and speak to my dad about it.

Thanks guys