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

MarcBK

New Member
Joined
Apr 19, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,164
Office Version
  1. 365
Platform
  1. Windows

MarcBK

New Member
Joined
Apr 19, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Something like this should do it,
Cell Formulas
RangeFormula
A4:A8A4=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:B8B4=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

New Member
Joined
Apr 19, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,445
Messages
5,675,900
Members
419,591
Latest member
mersanko

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