Formula that matches multple criteria and pulls latest date value

[Jamidd1]

Hi Mr Excel,

I am currently looking for a formula that index matches multiple criteria (For example; Name & Course Name) & also looks for the latest date & returns the latest grade.
Please see example attached below. I tried to make up an example on here but I am having a bit of trouble with this text box.

Link to example - https://drive.google.com/file/d/1RldSdI-ageGpJG8G8nKt83KhmzfBOr2n/view?usp=sharing

Thanks in advance: Jamidd1

 

[Legacy 436357]

I found a way using a helper cell. I hope this works for you:

Excel Workbook
	F	G	H
2		Match Criteria 1	John
3		Match Criteria 2	5930
4
5		Result Desired
6	7/18/2019	EL3
Sheet1

 

[Jamidd1]

I found a way using a helper cell. I hope this works for you:

Sheet1

	F	G	H
	John
EL3

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 70.4px;"><col style="width: 70.4px;"><col style="width: 70.4px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="bgcolor: #FFFFCC"]Match Criteria 1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="bgcolor: #CCCCFF"]Match Criteria 2[/TD]
[TD="align: right"]5930[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="bgcolor: #99CC00"]Result Desired[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]7/18/2019[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F6 {=MAX(IF($A$2:$A$15=$H$2,IF($B$2:$B$15=$H$3,$D$2:$D$15,"")))} G6 =IFERROR(INDEX(E2:E15,MATCH(F6,D2:D15,0)),"") <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks for you help. I have had a play with this today and it does not seem to work as it just pulls the first grade that matches that date. I need to somehow adapt that formula to to index match the name date & course code.

 

[Legacy 436357]

I believe this does what you asked for.