returning a value based on a range of upper and lower limits

[Chris]

I have a range of upper and lower limits eg.
"0-1"=10
"1-2"=15
"2-3"=20
"3-4"=25
"4-5"=30

Each of these has a value assigned to it.

From a list of values I want excel to look through the ranges above and
return the corresponding value. So if the number on the list is 2.5 then
this is between 2 and 3 therefore the value returned is 25. Currently I
can do this using nested "IF" statements, however this is cumbersome and
very tediuos for a big range of numbers.

Is there a better way to do this?

Thanks



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Frank Kabel]

Hi
in column A just put your lower boundary. After this use
the formula
=VLOOKUP(2.5,A1:B20,2,1)

 

[Max]

Think using an IF with a VLOOKUP
(with the lookup col in ascending order and 4th arg set to TRUE)
would be one approach ..

In Sheet1
-----------
Create a lookup table in A1:B6 with the values:

0 10
1 15
2 20
3 25
4 30
5 Range exceeded

The above table implies a tighter, less ambiguous re-definition of your
specs, viz.:

Values >=0, <1 ---- return 10
Values >=1, <2 ---- return 15
Values >=2, <3 ---- return 20
Values >=3, <4 ---- return 25
Values >=4, <5 ---- return 30
Values >=5 ---- return "Range exceeded"

In Sheet2
-----------
Suppose your list of lookup values is in A1 down

Put in B1:
=IF(A1="","",IF(A1<0,"Range exceeded",VLOOKUP(A1,Sheet1!$A$1:$B$6,2,TRUE)))

Copy B1 down

Col B will return the corresponding values from the table in Sheet1 for the
list in col A
If there's no value in col A, i.e. cell is blank or contains a null string
(""), blanks ("") will be returned
If the value in col A is less than zero, the phrase "Range exceeded" will be
returned

 

[Max]

A little extra clarification ..

Col A in Sheet2 is assumed *not* to contain text

If there is a possibility that some numbers in col A may be in text format,
try instead in B1:

=IF(A1="","",IF(A1+0<0,"Range
exceeded",VLOOKUP(A1+0,Sheet1!$A$1:$B$6,2,TRUE)))

Text other than "numbers" will return #VALUE!

 

[Dana DeLouis]

"2-3"=20

So if the number on the list is 2.5 then
this is between 2 and 3 therefore the value returned is 25.

Do you mean 25, or 20 from the table?

Your data appears linear. If it's 20 from the table, would this work?

=INT(A1)*5+10

HTH
Dana DeLouis

 

[Max]

Do you mean 25, or 20 from the table?

I took it the above was a typo in the post by the OP,
that the limits table was the correct reference
[ value returned should be "20"]
Believe so did Frank <g>