Index Match to Return Second To Last Non-Numerical Entry-CodePudding

My team has to fill in their schedules in google forms each week, and basically I'm trying to do it so if they select "Maintain," I can automatically pull their last/previously filled entry.

So far I've gotten this: =if(D23="Maintain",(INDEX('Form Responses'!$E$2:$R,MATCH($C23,'Form Responses'!$B$2:$B)-1)),(INDEX('Form Responses'!$E$2:$R,MAX(IF('Form Responses'!$B$2:$B=C23,ROW(C:C)))))) but the "True" section isn't returning the right row (with or without the "-1").

The name "JC" is supposed to show the data from row32 in the responses sheet, and "NA" is supposed to return that of row40.

The "-1" is supposed to Index/Match the second-to-last entry with that specific name, but I can't tell if it's working correctly or not.

What am I doing wrong with the "True" portion?

Sample sheet:

CodePudding user response：

This will help you even if it is not the exact answer you are expecting

=query('Form Responses'!A:R,"select * where B matches '"&textjoin("|",,filter('Form Responses'!B:B,'Form Responses'!C:C="Maintain"))&"' order by B asc, A desc ",1)

it's possible by adding a new column and a formula to reduce the numbrer of rows after "maintain"