If Function

[jrdng01]

Hello everyone,

I am trying to use if function however Nested If is limited to 7. Ho
do I over come this. Here is the data

Date:
01/02/2016
01/02/2016
07/02/2020
07/02/2020
07/02/2015
02/02/2015
12/02/2015
12/02/2015
10/02/2017
01/02/2016
-----------
Maturity Bucket:
0 to 1 month 1-Mar-13
1 to 3 months 1-May-13
3 to 6 months 1-Aug-13
6 to 12 months 1-Feb-14
1 to 2 years 1-Feb-15
2 to 3 years 1-Feb-16
3 to 4 years 1-Feb-17
4 to 5 years 1-Feb-18
5 to 7 years 1-Feb-20
7+ years 2-Feb-20

Here is my formulae:
=if($U8<$AB$2,$AA$2,if($U8<$AB$3,$AA$3,if($U8<$AB$4,$AA$4,if($U8<$AB$5,$AA$5,if($U8<$AB$6,$AA$6,if($U8<$AB$7,$AA$7,if($U8<$AB$8,$AA$8,if($U8<$AB$9,$AA$9,If($U8<$AB$10,$AA$10,if($U$8>$AB$10-1,$AA$11))))))))))

I am trying to check condition and have use concatenate such as & but i
still not working.
Here is the formulae for &:
=IF($U3<$AB$2,$AA$2,"") &IF($U3<$AB$3,$AA$3,"") &IF($U3<$AB$4,$AA$4,""
&IF($U3<$AB$5,$AA$5,"") &IF($U3<$AB$6,$AA$6,"") &IF($U3<$AB$7,$AA$7,""
&IF($U3<$AB$8,$AA$8,"") &IF($U3<$AB$9,$AA$9,""
&IF($U3<$AB$10,$AA$10,"") &IF($U3>($AB$10-1),$AA$11,"")

Outcome which I don't want as below
2 to 3 years3 to 4 years4 to 5 years5 to 7 years

I just want if the condition falls within the Maturity Bucket, retur
with only One value where it applicable. ie; if 10/02/2017 should retur
as 3 - 4 years etc.

Thank

 

[MyVeryOwnSelf]

I am trying to use if function however Nested If is limited to 7. How

do I over come this.

One way is to avoid using IF and instead use something like this:
=INDEX($AA$2:$AA$11,MATCH($U3,$AB$2:$AB$11,1))

Hope this helps getting started.