excel - 根据Excel中的条件返回日期(Return a date based on criteria in excel)

Question

I have a table of athlete names, training sessions and dates they completed the sessions.

(我有一张运动员姓名，训练课程和他们完成课程的日期的表格。)

From this table i want to extract the most recent date (excluding the current date) that a specific athlete completed a specific session.

(我想从该表中提取特定运动员完成特定训练的最近日期（当前日期除外）。)

So far my formula is as follows:

(到目前为止，我的公式如下：)

=IF(MATCH($AC27&$AD27, 'Threshold Efforts Data Entry'!$A$2:$A$2000&'Threshold Efforts Data Entry'!$BS$2:$BS$2000, 0), MAX(('Threshold Efforts Data Entry'!$B$2:$B$2000 <$AE$1)*'Threshold Efforts Data Entry'!$B$2:$B$2000),)

AC27 - specific athlete name AD27 - specific athlete session, A2:A2000 - lookup array for athlete names, BS2:BS2000 - look up array for sessions, B2:B2000 - look up array for dates, AE1 - the current date

(AC27-特定运动员名称AD27-特定运动员课程，A2：A2000-查找运动员名称的数组，BS2：BS2000-查找课程的数组，B2：B2000-查找日期的数组，AE1-当前日期)

The formula works, however it returns the most recent date, rather than the most recent date matched for the athlete name and session.

(该公式有效，但是会返回最近的日期，而不是返回与运动员名称和会话匹配的最新日期。)

Where have I gone wrong ?

(我哪里出问题了？)

  ask by Sophie translate from so

Answer 1

This works for me.

(这对我有用。)

=MAX(IF($A$5:$A$12=D5,IF($B$5:$B$12<$B$1,$B$5:$B$12)))

It's an array function, so click in the cell and press ctrl + shift + enter.

(这是一个数组函数，因此在单元格中单击，然后按Ctrl + Shift + Enter。)