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
 

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

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top