# VLOOKUP question

#### xcellnoob

##### Board Regular
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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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

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.

=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.

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.

Replies
2
Views
355
Replies
7
Views
822
Replies
2
Views
132
Replies
5
Views
482
Replies
1
Views
605

1,219,800
Messages
6,150,322
Members
450,951
Latest member
kh198

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back