Lookup unqiue ID between sheets to obtain most recent date

[cwim1990]

Hi, I am looking to use a formula to lookup a unique id from one sheet, to the same id in another sheet to obtain the most recent date from the second sheet.
Example is a user id- to most recent appointment date where there are multiple appointment dates (and filtering is not an option) . Have tried: =MAX(IF(B3,'Sheet2'!AC2)} & =vlookup(max(date,B2,'Sheet2'!K:AC,19,0). The first formula gave me dates, but upon checking were not the most recent. Any help appreciated

 

[Saba Sabaratnam]

You could use MAXIFS to get the most recent date.

I have this data in one worksheet and in another sheet, I have this formula in B3

=MAXIFS(Sheet4!B:B,Sheet4!A:A,Sheet5!A3)

Kind regards

Saba


Kind regards

Saba

 

[cwim1990]

You could use MAXIFS to get the most recent date.

I have this data in one worksheet and in another sheet, I have this formula in B3


View attachment 43096


View attachment 43097




=MAXIFS(Sheet4!B:B,Sheet4!A:A,Sheet5!A3)

Kind regards

Saba


Kind regards

Saba

Thanks Saba,

Having issues with this formula, my sheet 1 has various user ID's in column B and am trying to match those ID's with Sheet 2 along with obtaining the most recent date so have tried: =maxifs(B2,'sheet2'!K:AC,19,0) &=maxifs(B2,'sheet2'!K:AC,19) getting a #VALUE error. Unsure if this formula is able to lookup and match the various user ID's on each page.

Trying to match user ID on Sheet 1 - B2 with user ID on Sheet 2 Column K & Most recent date on Column AC (sheet 2) ( AC is 19 columns from column K)

 

[Saba Sabaratnam]

I think that you will have to change the formula in Sheet 1 to

=MAXIFS(Sheet2!AC:AC,Sheet2!K:K,B2)

Kind regards

Saba

 

[cwim1990]

I think that you will have to change the formula in Sheet 1 to

=MAXIFS(Sheet2!AC:AC,Sheet2!K:K,B2)

Kind regards

Saba

YES!!! Thanks so much I was going backwards!

 

[Saba Sabaratnam]

No problem

 

[cwim1990]

No problem

Hey Saba, hoping you could help now, with the same formula as above BUT the oldest date ?

 

[Saba Sabaratnam]

No problem, it is MINIFS instead of MAXIFS.

Kind regards

Saba