Hlookup formula trouble

[Willmonbo]

I need to have the months of the year arranged in different ways for different employees, meaning , every employee has their year start at the month of hire, so at cell A1:A12 I have the months of the year and in A1 I have a data validation list of all months of the year residing at Z1:Z12 (which I named MonthList) and I did the following formula in cell A2

=IF(A1=Z12,Z1,HLOOKUP(A1,MonthList,MATCH(A1,MonthList,0)+1))

I intended to copy this formula down to all cells till A12, but so far the formula in A2 works well when I select some months from the data validation drop down list in A1 but does not work with other months and I can't figure out what causes it to behave the way it does.

I'll appreciate any suggestion as to why this doesn't work as well as any suggestion as to how I can do it differently, thanks in advance.

 

[njimack]

Have you tried simply linking to the cell containing their start date?
=DATE(YEAR(A1),MONTH(A1)+1,1)

(unless I've completely misunderstood what you're trying to do!)

 

[Willmonbo]

I am not too familiar with the Month function but I just tried it the way you wrote out the formula and it returns a #value error, any suggestions?

thanks for your help

 

[njimack]

Which cell contains the employee start date and is it an actual date (i.e 01/01/10) rather than text (i.e "Jan")

 

[Willmonbo]

the actual start date is not in excel I am simply trying to build a utility that i can use to run some calculations but it's important to this that I can start the year at any month, so I just created a list of months and pointed cell A1 to do a data validation to the list of months.