VLOOKUP question

[xcellnoob]

So, first off, thanks in advance for any help.
I have a workbook with 5 tabs that correspond to the weeks in a month.
I want a cell at the top of each page to display the fiscal week number.
There are five tabs because some months have 5 weeks.
It's an easy enough vlookup function to populate the week numbers based on the calendar month(#).
However......
What if a month doesn't have a fifth week? I don't want anything to be displayed at the top of that tab.
I set up my table as:
9_ 40
__ 41
__ 42
__ 43
10_44
__ 45
__ 46
__ 47
__ 48
etc
As it is right now, month 9 only has four weeks, but the 5th tab will display 44 thinking that there is a 5th week anyway. I want it to not populate anything.
I hope this makes sense. Help? I was going to try an if, but I don't know how to formulate it.
Thanks

 

[xcellnoob]

Another problem that I just ran into.
I was referring to a cell where the date entered is in the format of mm-yy.
That was fed to a cell which showed it as m.
So, 09-06 shows up as 9
Now when I reference the cell with 9 in it, the formula produces the result of 39681 or something. Anyway around this?
Thanks

 

[NBVC]

Another problem that I just ran into.
I was referring to a cell where the date entered is in the format of mm-yy.
That was fed to a cell which showed it as m.
So, 09-06 shows up as 9
Now when I reference the cell with 9 in it, the formula produces the result of 39681 or something. Anyway around this?
Thanks

Format the cell as date....Format|Cells|Number tab....select Date and choose the format.


Please post the formula you are using currently in question 1.

 

[xcellnoob]

=VLOOKUP($D$3,$C$10:$D$20,2)
Where D3 is =C3 (formatted as "m")
C3 user inputs month in mm/yy format.
Hope this helps.

 

[NBVC]

if you fill in all the first column so that all cells have a corresponding month, then a formula like this could work:

=IF(ROWS($A$1:A1)>COUNTIF($C$10:$C$20,MONTH($C$3)),"",SMALL(IF($C$10:$C$20=MONTH($C$3),($D$10:$D$20)),ROWS($A$1:A1)))

confirmed with CTRL+SHIFT+ENTER not just Enter....you'll see {} brackets at the ends of the formula if done correctly.

then copy down.