IF Function question

[cpigrome]

So i want an IF function to return different values for different ranges for example;

I want it so that:
IF D2 <= 999999, return "0-1m"
IF D2 >= 1000000 but < 1999999, return "1m-2m"
IF D2 >= 2000000 but < 4999999, return "2m-5m"
IF D2 >= 5000000 but < 9999999, return "5m-10m"
IF D2 >= 10000000 but < 14999999, return "10m-15m"
IF D2 >= 10000000 but < 19999999, return "10m-20m"
IF D2 >= 20000000 but < 30000000, return "20m-30m"
IF D2 >= 30000000, return "30m+"

Quite a simple question but could someone show me the function that would do this? When i try i cant get it working.

Thanks in advance!

 

[MarcelBeug]

You'd better create a list with 2 columns, the first column being the lower limit of your ranges, the second column the descriptions.
For the lower limit use 0.
Suppose this is column Y and Z, enter formula

=VLOOKUP(D2,Y:Z,2)

Edit: avoid overlapping ranges like 10m-15m and 10m-20m

 

[Jonmo1]

Try the LOOKUP funciton instead..

=LOOKUP(D2/1000000,{0,1,2,5,10,15,20,30},{"1m-2m","2m-5m","5m-10","10m-15m","10m-20m","20m-30m","30m+"})

 

[cpigrome]

=IF(D2<=999999,0-1m,IF(AND(D2>=1000000,D2<=1999999),1m-2m,IF(AND(D2>=2000000,D2<=4999999),2m-5m,IF(AND(D2>=5000000,D2<=9999999),5m-10m,IF(AND(D2>=10000000,D2<=14999999),10m-15m,IF(AND(D2>=15000000,D2<=19999999),15m-20m,IF(AND(D2>=20000000,D2<=29999999),20m-30m,30m+)

I know its a long IF but i thought that this would work but it doesnt? Is there a reason why?

 

[Zakkaroo]

All of your results need quotation marks

e.g. =IF(D2<=999999,"0-1m",IF(AND(D2>=1000000,D2<=1999999),"1m-2m"

etc.

 

[cpigrome]

Thanks for your help mate

 

[Jonmo1]

You should consider the far simpler lookup method.
Though I left off the 0-1m in my post..
Should be

=LOOKUP(D2/1000000,{0,1,2,5,10,15,20,30},{"0-1m","1m-2m","2m-5m","5m-10","10m-15m","10m-20m","20m-30m","30m+"})