I have a sheet with 2 multi-select columns. The value options for the 2 columns are the same, and are listed in a 2nd sheet. In the second sheet, I'd like to write a formula that finds the most recent date where the value is present in one of the two multiselect columns. Once I have that data, I'd like to know which column it came from. If the requirement doesn't exist in either column, I'd like to return "Not Complete"
Since a picture may be easier to understand, here is some dummy data from my two multi-select columns in my primary sheet
And my 2nd sheet (ignore column 6, this formula uses IF(CONTAINS( but will not work because it doesn't account for the fact a requirement may be listed as having issues later in the sheet and the most recent result is the one I'm interested in):
My initial thought was to find the max date where the value is in the "requirements with issues" column, then a separate formula for max date where the value is in the "requirements with no issues". Then compare the dates in a 3rd column to determine which is max between the 2 to be able to assign a value of "Passed" or "Not Complete"
My Most recent fail formula that isn't working:
=MAX(COLLECT({TestDate}, {Reqs with issues}, HAS({Reqs with issues},[Column5]@Row)))
where {TestDate} is another date column in my primary sheet
Is there a better approach for me to determine the most recent categorization for a specific requirement?
