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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Apples[/TD]
[TD]ordered[/TD]
[TD]2/4/2019[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]backfilled[/TD]
[TD]2/4/2019[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]arrived[/TD]
[TD]3/4/2019[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]arrived[/TD]
[TD]3/4/2019[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]backstock[/TD]
[TD]4/4/2019[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]missing[/TD]
[TD]4/4/2019[/TD]
[/TR]
</tbody>[/TABLE]

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))

[TABLE="width: 366"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Type[/TD]
[TD]Result[/TD]
[TD]Dates[/TD]
[TD][/TD]
[TD]Criteria[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]ordered[/TD]
[TD="align: right"]2/4/2019[/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD]backstock[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]backfilled[/TD]
[TD="align: right"]2/4/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]arrived[/TD]
[TD="align: right"]3/4/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]arrived[/TD]
[TD="align: right"]3/4/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]backstock[/TD]
[TD="align: right"]4/4/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]missing[/TD]
[TD="align: right"]4/4/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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?