Vlookup for the Latest date on multiple matches

[Corleone]

Hi

I am using the following formula but im trying to get it to pick up the latest date that it matches (in most cases there are multiple matches)
=VLOOKUP(K3,'All Milestone Report'!$U$18:$AF$11968,3,0)
The source data is in Date order

Where am i going wrong?

I have tried changing in to the approximate setting which works if there are matches in the source data but it also populates rows where there are no matches...
=VLOOKUP(K3,'All Milestone Report'!$U$18:$AF$11968,3,TRUE) but this doesnt work as it brings up incorrect matches



Thanks

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Corleone]

Updated - Im using Excel 365 - Ta

 

[Fluff]

Thanks for that, how about

=MAXIFS('All Milestone Report'!$W$18:$W$11968,'All Milestone Report'!$U$18:$U$11968,K3)

what would you want to appear if there is no match?

 

[Corleone]

Thanks for that, how about

=MAXIFS('All Milestone Report'!$W$18:$W$11968,'All Milestone Report'!$U$18:$U$11968,K3)

what would you want to appear if there is no match?

Cheers - i would like 0 of there is no match

 

[Fluff]

In that case the formula should be fine.

 

[Corleone]

Just about to log onto a meeting - ill give it a go in an hour or so and let you know how i get on- thanks

 

[Fluff]

Ok

 

[Corleone]

Ok

That works perfectly - cheers - if i want to make it so it returns a blank rather than 0 - do i just add ,"" at the end? - Thanks Again

 

[Fluff]

To have a blank try

=IFERROR(1/(1/MAXIFS('All Milestone Report'!$W$18:$W$11968,'All Milestone Report'!$U$18:$U$11968,K3)),"")