vlookup base on a maximum/latest condition

cantab429

New Member
Joined
Mar 24, 2019
Messages
8
Hi guys,

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

Applesordered2/4/2019
Orangesbackfilled2/4/2019
Applesarrived3/4/2019
Orangesarrived3/4/2019
Applesbackstock4/4/2019
Orangesmissing4/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.!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
737
Office Version
  1. 365
Platform
  1. Windows
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):
Code:
=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))

TypeResultDatesCriteriaResult
Applesordered2/4/2019Applesbackstock
Orangesbackfilled2/4/2019
Applesarrived3/4/2019
Orangesarrived3/4/2019
Applesbackstock4/4/2019
Orangesmissing4/4/2019

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>
 
Last edited:

niamakutaku

New Member
Joined
Oct 18, 2011
Messages
1
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?
 

Forum statistics

Threads
1,141,203
Messages
5,704,941
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top