Compare dates in two different sheets and return a value

Janet Skjerven

I have a reporting use case where the project managers have to enter a reporting code so that reporting roll up occurs in the correct fiscal quarter. I want to automate the assignment of the reporting code so project managers do not have enter the code . I created a sheet that contains a reporting code for fiscal years and quarters, and then want to test the projected start date against the reporting code start date. So essentially,

if {reporting code start date} greater than or equal to [Projected Start Date]@ row, return the corresponding reporting code.

Same for

if {reporting code end date} less than or equal to [Projected End Date]@ row, return the corresponding reporting code.

Screen shot of the fiscal year/quarter table is below:

Any help is truly appreciated.

Regards,

Janet

Paul Newcome

Assuming you are wanting to populate "22Q2" there on row 1 because the Projected Start is 10/01/21, you are going to want something like this...

=INDEX({Table Sheet Code Column}, MATCH(MAX(COLLECT({Table Sheet Start Column}, {Table Sheet Start Column}, @cell<= [Projected Start]@row)), {Table Sheet Start Column}, 0))

Paul Newcome

Are you able to provide a screenshot of the main sheet for reference?

Janet Skjerven

Hi Paul, my apologies for failing to include a screenshot of the main plan, see below.

Paul Newcome

Assuming you are wanting to populate "22Q2" there on row 1 because the Projected Start is 10/01/21, you are going to want something like this...

=INDEX({Table Sheet Code Column}, MATCH(MAX(COLLECT({Table Sheet Start Column}, {Table Sheet Start Column}, @cell<= [Projected Start]@row)), {Table Sheet Start Column}, 0))

Janet Skjerven

Thank you Paul, that worked like a charm.

Paul Newcome

Happy to help. 👍️