Date Range

Shahara Anderson

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!

Paul Newcome

@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))

Shahara Anderson

@Paul Newcome That worked beautifully! Thank you so very much. Thank you for replying so quickly.

Paul Newcome

The date of 3/30/20 would pull both b and c. How would you want that handled?

Phips Allison

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

Shahara Anderson

Hi Paul,

Thank you for replying. I would like to take the one with the latest date.

Thanks,

Shahara

Paul Newcome

@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

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

Shahara Anderson

@Paul Newcome That worked beautifully! Thank you so very much. Thank you for replying so quickly.

Paul Newcome

Great news. Happy to help. 👍️