Date Range
I am trying to look up a date in Sheet 1 and return a permit number from Sheet 2 if the date from sheet 1 falls with a date range on sheet 2.
Sheet 1
Sheet 2
Thanks!
Best Answers
-
@Shahara Anderson So would it work if we referenced the latest End Date where the Start Date overlaps the date shipped?
For this we would start with...
=MAX(COLLECT({Other Sheet End Date Column}, {Other Sheet Start Date Column}, @cell <= [Date Shipped]@row))
This will give us our date. Then we would use an INDEX function to pull from the Permit Number column and a MATCH function referencing the previously pulled date to determine which row is needed for the INDEX function.
=INDEX({Other Sheet Permit Number Column}, MATCH(date formula, {Other Sheet End Date Column}, 0))
Which results in:
=INDEX({Other Sheet Permit Number Column}, MATCH(MAX(COLLECT({Other Sheet End Date Column}, {Other Sheet Start Date Column}, @cell <= [Date Shipped]@row)), {Other Sheet End Date Column}, 0))
-
@Paul Newcome That worked beautifully! Thank you so very much. Thank you for replying so quickly.
Answers
-
The date of 3/30/20 would pull both b and c. How would you want that handled?
-
Hi - please let me know how this was done.
I have a sheet with rates per hour for each employee but we need to have them changing over time, the time periods wouldn't cross over. I need to be able to pick the correct pay rate for the date the work was done for the correct employee.
Thanks so much
-
Hi Paul,
Thank you for replying. I would like to take the one with the latest date.
Thanks,
Shahara
-
@Shahara Anderson So would it work if we referenced the latest End Date where the Start Date overlaps the date shipped?
For this we would start with...
=MAX(COLLECT({Other Sheet End Date Column}, {Other Sheet Start Date Column}, @cell <= [Date Shipped]@row))
This will give us our date. Then we would use an INDEX function to pull from the Permit Number column and a MATCH function referencing the previously pulled date to determine which row is needed for the INDEX function.
=INDEX({Other Sheet Permit Number Column}, MATCH(date formula, {Other Sheet End Date Column}, 0))
Which results in:
=INDEX({Other Sheet Permit Number Column}, MATCH(MAX(COLLECT({Other Sheet End Date Column}, {Other Sheet Start Date Column}, @cell <= [Date Shipped]@row)), {Other Sheet End Date Column}, 0))
-
@Phips Allison From the sound of things, your solution may end up being a little different. Could you provide some screenshots of the source and target sheets with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed?
This will better help to visualize exactly what you are working with and what your desired outcome is.
-
@Paul Newcome That worked beautifully! Thank you so very much. Thank you for replying so quickly.
-
Great news. Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!