Excel - Lookup against a range

Martynt74

Martynt74

Martynt74

Soldato
Joined
20 Feb 2004
Posts
23,362
Location
Hondon de las Nieves, Spain
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
 
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.
 
Maybe I'm missing something could you just make a table like

1 low
2 low
3 medium
4 medium
Etc

And then do a vlookup on that table
 
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.
 
gillywibble said:
Hence my question :)
Click to expand...

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
 
You need to be careful with some of the ranges in there, you have a couple of repeated numbers (1.3 and 3.12). I'm also assuming that the upper value for the lower of 0.22 shouldn't be 0.93!

You can use a nifty function called SUMPRODUCT to achieve this, which is essentially a multiple variable vlookup. For this table I have used =SUMPRODUCT(--(G2>=$A$2:$A$60),--(G2<=$B$2:$B$60),$C$2:$C$60) where G2 is the cell with the value I am using to lookup.

Hope this helps!
 
Assuming that table starts at cell A1, the formula you want is:
Code: 
=INDEX(C$2:C$60,MATCH(value,B$2:B$60,1)+1)
where 'value' is what you're looking for.

for a 'value'=1, the above returns 84
 
You must log in or register to reply here.
Back
Top Bottom