Use Max Collect to find most recent record with value in multiselect columns

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?



  • Also curious if max row index would work better (does such a formula exist?) in case the same requirement is listed in two separate rows with the same date?

  • HeatherD.
    HeatherD. Moderator

    Hi @jthompson !

    You're really close with the MAX/COLLECT formula you created! Try using @cell within the HAS function:

    =MAX(COLLECT({TestDate}, {Reqs with issues}, HAS(@cell, [Column5]@row)))

    That should resolve the issue with your formula. You can then reuse the formula and replace {Reqs with issues} with a range referencing your Requirements Test with No Issues column to pull the latest date for the most recent pass date. Of course, you'll want to be sure you delete the {Reqs with issues} range name, including curly brackets, and click "Reference another sheet" instead of clicking "Edit reference" to avoid accidentally editing the existing range.

    Hope this helps!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!