VLOOKUP question

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Board Regular
Joined
Apr 6, 2005
Messages
188
=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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,956
Messages
5,545,177
Members
410,668
Latest member
Gaexel
Top