lookup based on previous and current month

[xljgd]

I have a vlookup which changes values in the table based on the month. I have the month in the list box and the user selects it and the results change.


I have 2 lists Current ap and previous ap.


When the user select the current AP say May than the values change. i am having problems trying to automatically show the previous AP. In my list drop down box i only have the month from Jan , feb thru dec. I dont have any date to do my calculations. How to get the previous AP only with the month field. i.e if i select May i should get April as the previous ap selected and the values change based on the previous month.


I tried to base my formula on today() and extract the text(today(),"mmm_")) but i cannot do it because the user ideally selects the list from the drop down list box. can anyone help . I need to have some formula that derives the previous month (May-1 =April). My vlookups work but i have to automatically get previous ap values too.

 

[NARAYANK991]

Hi ,


1. Define a named range Months , and enter the following formula in the Refers To box :


={"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}


2. Now use the following formula to derive the previous month :


=IF(MATCH(G7,Months,0)=1,INDEX(Months,12),INDEX(Months,MATCH(G7,Months,0)-1))


where G7 is an arbitrary cell address which will contain the current month ; if G7 contains May , then the above formula will return Apr. If G7 contains Jan , then the above formula will return Dec.


Narayan

 

[xljgd]

Thanks a million. that worked like a charm. i would never have been able to figure that out. so if i want 2-3 months before i have to minus from the array.


thanks again.

 

[NARAYANK991]

Hi ,


Yes ; only take care to wrap around ; at present , the wrap around is only one month , so if the current month is Jan , the previous becomes Dec. If you are going to allow previous months going back two or even three months , you will have to take care of the wrap around if the current month is Feb / Mar and you want to end up with Dec / Nov / Oct.


Narayan

 

[Haseeb A]

Hi xljgd,


Another way,


=TEXT(DATE(9999,MONTH(1&A1)-Number_of_Months,1),"mmm")


Change Number_of_Months. This could also refer >12 months ie, -13, -25, -29 months etc...


If you want to add months, then change the year to 1904 & - to +


=TEXT(DATE(1904,MONTH(1&A1)+Number_of_Months,1),"mmm")


Regards,

Haseeb

 

[Luke M]

Another way to skin the cat:

=TEXT(EOMONTH(1&A1,Number_of_Months),"mmm")


Number_of_Months can be any number, negative (to subtract) or positive (to add).

 

[Haseeb A]

FYI xljgd,


If you are using excel 2003 or previous versions, Analysis ToolPak AddIn is require to activated to work EOMONTH.