IF or Lookup

[Wombat]

Im trying to write a statement that tests for a series of
milestones since joining an organization and returns the
next due milestone. The milestones are 30 days, 60 days,
90 days, 6 months, 9 months, 1 year, 18 months, 5 years
and 10 years. I think there must be a better way than
what I'm attempting below and if someone could show me or
point me in the right direction I'd be extremely grateful.

I can get so far the following statement but
1: I run of nested IFs (7 max)
2: If I try to copy in another test for date + 12 months
I get an unspecified error

=IF(ISBLANK(B9),"",IF(B9+30>TODAY(),B9+30,IF(B9+60>TODAY
(),B9+60,IF(B9+90>TODAY(),B9+90,IF(DATE(YEAR(B9),(MONTH
(B9)+6),(DAY(B9)))>TODAY(),DATE(YEAR(B9),(MONTH(B9)+6),
(DAY(B9))),IF(DATE(YEAR(B9),(MONTH(B9)+9),(DAY(B9)))>TODAY
(),DATE(YEAR(B9),(MONTH(B9)+9),(DAY(B9))),"Check for 1
year anniversary"))))))

Thank You.

 

[duane]

I listed your milestones in d5:d14 and used this

=OFFSET($D$4,MATCH(TODAY(),D514,1)+1,0

 

[Wombat]

Thanks for your reply.

Could I impose a bit further and ask you to email the
spreadsheet you did to (e-mail address removed) so I can see how
it works. I don't use Excel much so getting my head
around some of the functions is a stretch for me.

Thanks

Wombat

 

[Biff]

Hi Wombat!

I'll send you a file that uses a different method.

Biff