# 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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### xcellnoob

##### Board Regular
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
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
=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
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
228
Replies
3
Views
188
Replies
0
Views
61
Replies
42
Views
1K
Replies
55
Views
593

1,148,191
Messages
5,745,257
Members
423,941
Latest member

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