Making If(find) statements work with shifting cells

[Kahn]

Hi,

I'm trying to get an if(find) statement to work where the target cell is an index, and thus changes when someone chooses are different variable. To be exact, I am building a dashboard where I need to show something for the last 12 months rolling. The 12 months depends on which current month is chosen via a drop down bar (which links to an indexed cell). I've entered the following formula:

=if(find("JAN",K6),"Feb-2011","") where K6 is my indexed cell and it works properly.

However if I want to expand this formula to look for any other months:

=if(find("JAN",K6),"Feb-2011",if(find("FEB",K6),"Mar-2011",etc,if(find("DEC",K6),"Jan-2012","") and I can't get it to work.

In fact, as soon as I add the second if string, it doesn't seem to work correctly for me.

The goal is for this to work and then build a seperate chart with which to use as a vlookup source for a graph.

Any suggestions? Any help would be appreciated!

Thanks,

[DGagnon]

try somethign like this..

=INDEX({"Feb-2012","Mar-2012","Apr-2012"},MATCH(K6,{"JAN","FEB","MAR"},0))

you just need to extend the arrays to the full year.

or you could put the arrays in a set of cells, and just reference it

[FDibbins]

if your dates are actual dates, you could use something like month()+1...and month+13... to select your start and end dates, andthen build yout formula around that?