Google Sheets | Index Match AND Max Date combined formula

Question

I am trying to find a formula to use in Google Sheets that would combine an Index Match and a Max Date formula. Attached a screenshot for reference.

Index would be 'Phase' column C Match would be 'ID Number' column B With an additional step of selecting the most recent/Max date of 'Last Modified Date' column A

The end result should be the same as the example 'Current Phase' column D.

Google Sheet

//copy of Array Data

/*Current Phase is the desired end result

Last Modified Date |ID Number| |Phase| |*Current Phase|
2/26/2022 165432 Welcome Health Check 3/1/2022 165432 Adoption Health Check 3/2/2022 165432 Health Check Health Check 2/26/2022 165432 Prep Health Check 2/26/2022 165432 Close Health Check 6/21/2022 412356 Welcome Adoption 6/23/2022 412356 Adoption Adoption 2/26/2022 412356 Health Check Adoption 2/26/2022 412356 Prep Adoption 2/26/2022 412356 Close Adoption

Answer 1

Answer

The following formula should create the desired behaviour:

=ARRAY_CONSTRAIN(FILTER($C$2:$C,$A$2:$A=MAX(FILTER($A$2:$A,$B$2:$B=$B2))),1,1)

Note that if multiple phases are tied for most recent, this will return only the first (i.e. lowest row number).

Explanation

The inner =FILTER selects only those rows from column A where column B has the same ID as the current row being evaluated. Then, the =MAX gets the most recent date from among those rows. Next, the outer =FILTER selects only those rows from column C where column A has the most recent date among all rows with the same phase (as determined by the =MAX). Finally, if there's a tie (multiple phases are tied for most recent date), =ARRAY_CONSTRAIN returns only the first.

Functions used:

• =FILTER
• =MAX
• =ARRAY_CONSTRAIN