Return value from Vlookup based on column where there is a Max date

[deacon10]

Hi there,

I am hoping one of you wonderful people can help me.

The data within Lat! in column B has a few duplicates and I am trying to return a value from columns 34 & 49 where the date in AD is the maximum.

Example data

A1= 123456
Lat!B2= 123456
Lat!B3= 123456
Lat!AD2= 10/05/18
Lat!AD3= 15/06/21

This is my formula so far, I am just not sure how to specify the MAX?

=IFERROR(IF(CG1<>"",VLOOKUP(A1,Lat!B:AO,34,FALSE),VLOOKUP(A1,Rec!:BX,49,FALSE)),"")

Any help always appreciated

 

[breynolds0431]

Hi there,

I am hoping one of you wonderful people can help me.

The data within Lat! in column B has a few duplicates and I am trying to return a value from columns 34 & 49 where the date in AD is the maximum.

Example data

A1= 123456
Lat!B2= 123456
Lat!B3= 123456
Lat!AD2= 10/05/18
Lat!AD3= 15/06/21

This is my formula so far, I am just not sure how to specify the MAX?

=IFERROR(IF(CG1<>"",VLOOKUP(A1,Lat!B:AO,34,FALSE),VLOOKUP(A1,Rec!:BX,49,FALSE)),"")

Any help always appreciated

You can accomplish with a max if array formula. The below will search for the value in A1 in the Lat tab's column B. It will then find the latest date from column AD. Note that the formula has the absolute referencing for columns B and AD. Update to what suits your needs.

{=MAX(IF(Lat!$B:$B=A1,Lat!$AD:$AD))}

***Also, be sure to complete the formula with Control + Shift + Enter

 

[Fluff]

How about

=INDEX(SORT(FILTER(Lat!AD1:AO10000,Lat!B1:B10000=A1),1,-1),1,12)

 

[deacon10]

You can accomplish with a max if array formula. The below will search for the value in A1 in the Lat tab's column B. It will then find the latest date from column AD. Note that the formula has the absolute referencing for columns B and AD. Update to what suits your needs.

{=MAX(IF(Lat!$B:$B=A1,Lat!$AD:$AD))}

***Also, be sure to complete the formula with Control + Shift + Enter

Thank you breynolds0431, is there away to adapt this into returning a value that isn't the date, from a different column (i.e. column C) to where the date is max in AD?

 

[deacon10]

How about

=INDEX(SORT(FILTER(Lat!AD1:AO10000,Lat!B1:B10000=A1),1,-1),1,12)

Further proof that you are an actual genius!

Thanks again Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.