Hello. In my spreadsheet I have two rows of information, one has the month
and year showing, and the other shows the corresponding month of the
project. Say if a project starts in May-06 (data in A4:DA4) the
corresponding month number is 1 (data in A3:DA3). When I enter a date in
cell A1 I need another cell (say B2) to show the corresponding project month
number. I can't seem to get it right. I can get the project month number to
show when I only have the month as a criteria, but not for the month and year
a criteria. Hopefully I am explaining this adequately. Can somebody help me
out with this please?

Thank you, JCS

Are there any suggestions to my problem? I have tried INDEX and MATCH, but
since I am unexperienced with these two functions I am getting the formula
wrong. Maybe I'm not explaining properly? Any help somebody could give me
would be greatly appreciated. This is my last formula to figure out on a big
project.

Thanks, JCS


"Fin Analyst" wrote:

Hello. In my spreadsheet I have two rows of information, one has the month
and year showing, and the other shows the corresponding month of the
project. Say if a project starts in May-06 (data in A4:DA4) the
corresponding month number is 1 (data in A3:DA3). When I enter a date in
cell A1 I need another cell (say B2) to show the corresponding project month
number. I can't seem to get it right. I can get the project month number to
show when I only have the month as a criteria, but not for the month and year
a criteria. Hopefully I am explaining this adequately. Can somebody help me
out with this please?

Thank you, JCS

I finally figured it out. In case anybody encounters a similar problem I
thought I would post how I solved it. There is probably a more efficient
way, but this is what I came up with.

I was trying to get a cell to output the project month number based on
another cell's date. The problem was that I needed the year and month to tie
to the months listed on top. My spreadsheet looks something like this:
1 2 3 4 (and so on for 120 rows)
project month #s
Jan-06 Feb-06 Mar-06 Apr-06 (and so on for 120 rows) calendar months

If the project starts construction in Apr-06 then I need my cell to return
4. There is another cell for the input of the construction start date on
another tab. What I did was create two extra lines. One that pulls the
calendar month number, and the other that pulls the year number. I then used
a formula from a website that Peo Sjoblom posted about match and index
functions. Here is the site address:
http://www.contextures.com/xlFunctions03.html
I used example number four on index and match. Thanks Peo for the site!

JCS

"Fin Analyst" wrote:

Are there any suggestions to my problem? I have tried INDEX and MATCH, but
since I am unexperienced with these two functions I am getting the formula
wrong. Maybe I'm not explaining properly? Any help somebody could give me
would be greatly appreciated. This is my last formula to figure out on a big
project.

Thanks, JCS


"Fin Analyst" wrote:

Hello. In my spreadsheet I have two rows of information, one has the month
and year showing, and the other shows the corresponding month of the
project. Say if a project starts in May-06 (data in A4:DA4) the
corresponding month number is 1 (data in A3:DA3). When I enter a date in
cell A1 I need another cell (say B2) to show the corresponding project month
number. I can't seem to get it right. I can get the project month number to
show when I only have the month as a criteria, but not for the month and year
a criteria. Hopefully I am explaining this adequately. Can somebody help me
out with this please?

Thank you, JCS