Vlookup to return date from data sheet if between certain date range.

[MarcBK]

Hello all,

I have a two sheet workspace. Sheet 1 is called Data and sheet 2 is Print Dates.

In Sheet 1 I have in multiple columns three sites. Site 1, 2 and 3. Site reference is found in row 3.

Columns A4:A80 are dates for Site 1, Columns C4:C80 are dates for Site 2 and Columns E4:E80 are dates for Site 3.

Columns B4:B80 are times for Site 1, D4:D80 are times for site 2 and G4:G80 are times for site 3.

In Sheet2: Print dates I have set columns, A4:A18 for dates to reflect Site 1, A20:A29 for dates for site 2 and A31:A39 for site 3 and columns B4:B18 for times for Site 1, B20:B29 for times for site 2 and B31:B39 for times for Site 3.

In Sheet 2 Cell G3 is a drop down for dates "from" and G4 is drop down for dates "to"

In sheet 2: print dates columns A I want to set a formula to extract date ranges from the data sheet if they are between the dates in G3:G4 and then in column B for the same sheet I want to return the time stamp that goes with that specific date.

Is someone able to help. I have tried Vlookups and Match forumlas with if statements but its got too much array lookups for my knowledge.

I have attached a link to my sheet for your viewing if needed. [Copy of Dates WIP.xls

your help is greatly appreciated.

 

[jasonb75]

Something like this should do it,

Copy of Dates WIP.xls
	A	B
3	Date	Time of Day
4	06/05/2021	Afternoon List
5	13/05/2021	Afternoon List
6	20/05/2021	Afternoon List
7	27/05/2021	Morning List
8
Print Dates

Cell Formulas
Range	Formula
A4:A8	A4	=IFERROR(AGGREGATE(15,6,Data!$A$4:$A$35/(Data!$A$4:$A$35>=$G$3)/(Data!$A$4:$A$35<=$G$4),ROWS(A$4:A4)),"")
B4:B8	B4	=IF(A4="","",VLOOKUP(A4,Data!$A$4:$B$35,2,0))

 

[MarcBK]

Something like this should do it,

Copy of Dates WIP.xls
	A	B
3	Date	Time of Day
4	06/05/2021	Afternoon List
5	13/05/2021	Afternoon List
6	20/05/2021	Afternoon List
7	27/05/2021	Morning List
8
Print Dates

Cell Formulas
Range	Formula
A4:A8	A4	=IFERROR(AGGREGATE(15,6,Data!$A$4:$A$35/(Data!$A$4:$A$35>=$G$3)/(Data!$A$4:$A$35<=$G$4),ROWS(A$4:A4)),"")
B4:B8	B4	=IF(A4="","",VLOOKUP(A4,Data!$A$4:$B$35,2,0))

Thanks a million.

In cell B20 on Print Dates sheet using the following formula =IF(A20="","",VLOOKUP(A20,Data!D4:D80,2,0)) it returns a N/A value.

 

[MarcBK]

Thanks a million.

In cell B20 on Print Dates sheet using the following formula =IF(A20="","",VLOOKUP(A20,Data!D4:D80,2,0)) it returns a N/A value.

Realized where i went wrong. Was entering D4:D80 not C4:D80. Thanks again.