dategoogle-sheetsmaxmaxifs

Google Sheets: Return latest date from one column depending on variables in 4 other columns


I am looking for a Google Sheet formula to help me find and return the date from another column, depending on variables from other multiple columns.

Here's a spreadsheet with my issue.

I would like Column B to be updated with the latest date in Column E, depending on variables from Columns F through I.

Thank you!

I am new to Google Sheets and I used this formula to gather data from one column only:

=IF(ISBLANK(A2),"", IF(maxifs($E$2:$E,$F$2:$F,"*"&A2&"*")=0, "", maxifs($E$2:$E,$F$2:$F,"*"&A2&"*")))

But it won't work if I change $F$2:$F to $F$2:$I, so I am missing something!


Solution

  • You can use:

    =ARRAYFORMULA(MAP(
       TOCOL(A2:A,1),
       LAMBDA(n,MAX(REGEXMATCH(F2:I,n)*E2:E))
     ))