Vlookup dates

[waxb18]

Hi Guys,

I have a dilemma which needs solving.

In a nushell i need to do a vlookup to extract week number from a set date range.
Not very well worded but the data below may make it more easier to visualise

<TABLE style="WIDTH: 184pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=245><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #003366; WIDTH: 56pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl74 height=19 width=74>Week No</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #003366; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl73 width=80>From </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #003366; WIDTH: 68pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl72 width=91>To</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=18>01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>02/07/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70>08/07/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=17>02</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>09/07/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl65>15/07/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=17>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>16/07/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl65>22/07/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=17>04</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>23/07/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl68>29/07/2011</TD></TR></TBODY></TABLE>
So if i had 03/07/2011, which sits between B2 and C2 i want to get 01.

At the moment im doing this manually which to do 52 times is becoming a right pain in back side.

If anyone knows a way to do the PLEASE PLEASE

 

[Kaps_mr2]

=OFFSET($E$17,MATCH(J18,$F$17:$F$22,1)-1,0,1,1)

assuming that your data is in the range e17:g22, and the date you are looking for is in J18. thanks

regards

Kaps

 

[waxb18]

Hi Kaps,
thanks for the help, but i still dont understand the variables.

Cell E17 is date Created?
Cell J18 is the week Number?
Range E17:F22 is week from/to???

Can you confirm this plz

 

[Kaps_mr2]

direct message me with your email address and I will send back the workbook so you can have a look.

 

[Aladin Akyurek]

Hi Guys,

I have a dilemma which needs solving.

In a nushell i need to do a vlookup to extract week number from a set date range.
Not very well worded but the data below may make it more easier to visualise

<TABLE style="WIDTH: 184pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=245><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #003366; WIDTH: 56pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl74 height=19 width=74>Week No</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #003366; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl73 width=80>From </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #003366; WIDTH: 68pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl72 width=91>To</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=18>01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>02/07/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70>08/07/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=17>02</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>09/07/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl65>15/07/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=17>03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>16/07/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl65>22/07/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=17>04</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>23/07/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl68>29/07/2011</TD></TR></TBODY></TABLE>
So if i had 03/07/2011, which sits between B2 and C2 i want to get 01.

At the moment im doing this manually which to do 52 times is becoming a right pain in back side.

If anyone knows a way to do the PLEASE PLEASE

=LOOKUP(E2,$B$2:$B$4,$A$2:$A$4)

where E2 houses a date of interest as look up value.

 

[bebo021999]

Try this:

=INDEX(A1:A6,MATCH(E1,B1:B6,1))

With E1 content criteria date.

 

[waxb18]

Sorry Guys im still not getting this.

I have week Number in column C
Week From in column D
Week to in Column E
and My dates in Column J

but some suggestions posted only reference 3 columns?

 

[Aladin Akyurek]

Sorry Guys im still not getting this.

I have week Number in column C
Week From in column D
Week to in Column E
and My dates in Column J

but some suggestions posted only reference 3 columns?

Since From dates in column D in ascending order, To dates are not needed...

In K2 enter and copy down:

=LOOKUP(J2,$D$2:$D$400,$C$2:$C$400)

where J2 houses a date of interest for which you want to determine the week number.

 

[waxb18]

Thanks guys!!!

 

[Aladin Akyurek]

Thanks guys!!!

You are welcome. Thanks for providing feedback.