vlookup base on a maximum/latest condition

[cantab429]

Hi guys,

I am having trouble with a formula to determine the latest value on a vlookup. The following is an example:

Apples	ordered	2/4/2019
Oranges	backfilled	2/4/2019
Apples	arrived	3/4/2019
Oranges	arrived	3/4/2019
Apples	backstock	4/4/2019
Oranges	missing	4/4/2019

<tbody>
</tbody>

I want to be able to write a formula thats based on the keyword "Apple" but look for the latest entry, so in this case, it will return "backstock".

I cant get this to work with even index match... any help would be greatly appreciated!

Thanks.!

 

[CyrusTheVirus]

Try out the below. You can copy/paste the below table into excel so you see how it operates.

F2 (Entered with Ctrl+Shift+Enter):

=INDEX($B$2:$B$7,MATCH(E2&MAX($C$2:$C$7*($A$2:$A$7=E2)),$A$2:$A$7&$C$2:$C$7,0))

Type	Result	Dates		Criteria	Result
Apples	ordered	2/4/2019		Apples	backstock
Oranges	backfilled	2/4/2019
Apples	arrived	3/4/2019
Oranges	arrived	3/4/2019
Apples	backstock	4/4/2019
Oranges	missing	4/4/2019

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[niamakutaku]

That formula works great! It shows me that I really need to understand array formulas better. Thank you, CyrusTheVirus!

Is there a way to stylize it so that it will ignore blank entries. Let's say the Results column has blank cells that may be associated with later dates in the Dates column. Is there a way to alter that to find only the most recent entry instead of returning a value of 0?