If Function

T

Trying To Excel

A B
1-5 $ 200.00
6-10 $400.00
11-15 $ 600.00
16-20 $ 750.00
21-30 $ 1000.00

Question - If the value in a cell in Column A falls within a specified range
then the corresponding $ amount is in Column B.
 
P

pdberger

Trying --

Howze about:

A B
1 5 $200
2 10 $400
3 15 $600
4 20 $800
5 25 $1000


10 X =VLOOKUP(A10,$A$1:$B$5,2)

hth
 
T

Trying To Excel

Trying To Excel said:
A B
1-5 $ 200.00
6-10 $400.00
11-15 $ 600.00
16-20 $ 750.00
21-30 $ 1000.00

Question - If the value in a cell in Column A falls within a specified range
then the corresponding $ amount is in Column B.
Click to expand...

I phrased my question incorrectly. Cell A1 could have any value in it from 1
to 30. My question is if that value is between lets say 1 and 5 then B1
should be $ 200.00. if it is between 16 and 20 then B1 should be $ 750.00 and
so on.
 
T

Toppers

Set up table as below in Columns A & B:

A B
1 200
6 400
11 600
16 750
21 1000

If lookup value is in D1 then:

=VLOOKUP(D1,$A$1:$B$5,2,1)

Or to allow for errors:

=IF(iserror(VLOOKUP(D1,$A$1:$B$5,2,1)),"",VLOOKUP(D1,$A$1:$B$5,2,1))

this return blank if error condition arises

HTH
 
V

vezerid

The formula suggested by pdberger does exactly what you ask. Just use a
separate area in your worksheet to populate with two columns exactly as
pdberger said. For example, put them in K1:L5. Then, if you enter a
value in A1, B1 will show the corresponding value with:

=VLOOKUP(A1,$K$1:$L$5,2)

HTH
Kostis Vezerides
 
V

vezerid

Oops,
too long away from computers... Both pdberger and myself are wrong in
the way we suggested you populate the lookup table. You should follow
Toppers' suggestion.

HTH
Kostis Vezerides
 
R

Ragdyer

OR ... include the list in the formula itself so that no separate datalist
is necessary:

=LOOKUP(A1,{0,1,6,11,16,21;0,200,400,600,750,1000})
 
Top