If function, multiple conditions

[melbin]

Hi...

Could you please help me to apply IF function with multiple conditions?

If the value in column D is in between 1 and 2, need "40" in column E.
If the value in column D is in between 2 and 5, need "80" in column E.
If the value in column D is in between 5 and 10, need "80" in column E.

I have attached the file and conditions are also described in it. Please help me to find a solution!!!

[JohnTopley]

Try

=IF(D2<=1,40,IF(D2<=2,80,IF(D2<=5,120,IF(D2<=10,160,200))))

[melbin]

Hi John,

Sorry... It is not working. I got 200 in all cells. Could you please help me with other formula?

[Paul]

You can use a LOOKUP formula to do this.

=LOOKUP(D2,{0,1,2,5,10},{40,80,120,160,200})

The first parameter is the value you want to check. (D2 in this case)
The second is an array of the lower limit of each grouping, e.g. 0 years, 1 year, 2 years, 5 years, 10 years.
The third is the result array, with each result linked to that same position in the first array, e.g. 0 --> 40, 1 --> 80, 2 --> 120, etc.

[melbin]

Hi Paul,

It is also working. Thank you so much for your help.

[JohnTopley]

See attached!

[melbin]

Thank you so much, John. It is working...!!!

[JohnTopley]

Look at results for 1,2 and 5 years with each formula : one of them is wrong as the results are different!