help with an IF/AND that uses cross-sheet references.

System

This discussion was created from comments split from: Multiple IF Statements.

RicqP

Hello all, I'm looking for help with an IF/AND that uses cross-sheet references.

I have a MEMBER ROSTER main sheet like this:

And I have a lookup sheet GRANT YEARS like this:

I am trying to pull the correct "Grant Year" column from the GRANT YEARS sheet to the main sheet MEMBER ROSTER based on a value in the MEMBER ROSTER "Approved Date" column. If the MEMBER ROSTER "Approved Date" falls between the "Start Date" and "End Date" in GRANT YEARS, I want the matching "Grant Year" from GRANT YEARS pulled to the MEMBER ROSTER "Grant Year" column.

I have added these sheet references to MEMBER ROSTER:

I have this formula as a starting point in my MEMBER ROSTER "Grant Year" column --

=IF(AND([Approved Date]:[Approved Date] <= {GRANT YEARS | End Date}, [Approved Date]:[Approved Date] >= {GRANT YEARS | Start Date}), {GRANT YEARS | Grant Year},"unknown grant year")

-- which gives me #INVALID OPERATION. I think I need to be including some INDEX/MATCHes but I'm not sure where or how to use them here.

Any help would be appreciated.

Paul Newcome

Try this:

=MAX(COLLECT({Grant Year}, {Start Date}, @cell <= [Approved Date]@row))

RicqP

This works - thank you! I hadn't considered using a combination of MAX and COLLECT.

Paul Newcome

Happy to help. 👍️