If with Range

[Adnan Halai]

Hello Experts,

I am working with If function wherein If any one can help with shortcut to complete my action. Below is the table I have. I want to create dynamic formula from Table itself. Is it possible. I already have formula and its working fine.



$0-5000 1.2
$5001-9000 1.3
$9001-14000 1.4
$14001-19000 1.5
$19001-25000 1.6
$25001-35000 1.7
$35000+ 1.8



=If(B12<0,"Invalid Input",if(ANd(B12>=0,B12<=5000),1.2,if(ANd(B12>=5001,B12<=9000),1.3,
if(ANd(B12>=9001,B12<=14000),1.4,if(ANd(B12>=14001,B12<=19000),1.5,
if(ANd(B12>=19001,B12<=25000),1.6,if(ANd(B12>=25001,B12<=35000),1.7,
if(B12>=35001,1.8,""))))))))

 

[Chihiro]

Create table using Floor value (or rather ceiling of previous group).

Then use Lookup.
Note addition of small value to Lookup range.

 

[bosco_yip]

Or, using MATCH() instead of LOOKUP()……..

=IFERROR(MATCH(B12,{0,5001,9001,14001,19001,25001,35001})*0.1+1.1,"Invalid Input")

Regards
Bosco

 

[David Evans]

This has been done in past examples using a User Defined Function (UDF) - let me know if that is of interest, and I'll dig up the material on it ...

It's a regular request around here - usually for Fees or Commission schedules