MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Returing a value using multiple range conditions


Posted by Mark on February 07, 2002 6:40 PM

I am trying to figure out how I would write a formula to do do the following;

I am looking for excel to automatically populate a cell with a fee amount based on the cost of an item. For example,

If the cost of an item is greater than .01 and less than 9.99, then the fee = .30

if the cost of an item is greater than 10 and less than 24.99, then the fee = .55

if the cost of an item is greater than 25 and less than 49.99, then the fee = 1.10


Posted by anno on February 07, 2002 6:59 PM

mark
think this will work but note that returns #N/A if referent cell is blank or less than .01:
=VLOOKUP(A1,{0.01,0.3;10,0.55;25,1.1},2)
come to think of it, doesn't meet your >25<49.99 condition either, but a start anyway.

Posted by anno on February 07, 2002 7:09 PM

this is uglier but meets all your needs i think...

ERR

Posted by anno on February 07, 2002 7:20 PM

less ugly - have switched brain on now...

sometimes it takes a while, but i think this is it:
=VLOOKUP(A1,{0,0;0.01,0.3;10,0.55;25,1.1;50,"Out of range"},2)

Posted by Mark on February 07, 2002 7:21 PM

Anno,
Thanks. I got it to work.

Posted by Mark on February 07, 2002 7:22 PM

Anno,
Thanks. I got it to work.