IF MAX to find most recent

[wizardmagu]

I have a file that I use and have a formula to find the most recent "Effective Date" based on the text in the "Header" column. The issue I have is that column B "Resource" is also unique and in the past I would filter the sheet and paste each resource to its own tab. I'm sure these is a way to get this done while all in the same sheet... Any thoughts on changing my current formula to also use column B as a requirement for a match

 

[JamesCanale]

MrExcelPlayground19.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Header	Resource					Effective Date			Most Recent
2	2121232	1					5/29/2000			No
3	2121232	1					6/30/2014			Yes
4	2121232	7					5/29/2000			Yes
5	2121621	1					5/29/2001			Yes
6	2121621	7					5/29/2000			Yes
7	2199433	1					9/3/2015			Yes
8	2199433	7					1/1/2010			No
9	2199433	7					10/3/2015			Yes
Sheet23

Cell Formulas
Range	Formula
J2:J9	J2	=IF(MAX(FILTER($G$2:$G$9,($A$2:$A$9=A2)*($B$2:$B$9=B2)))=G2,"Yes","No")

Or you could just add ($B$2:$B$3000=B2) also in your formula.

 

[Fluff]

You could also use

=IF(MAXIFS(G:G,A:A,A2,B:B,B2)=G2,"Yes","No")

 

[wizardmagu]

They both worked great!

I am trying to follow this formula to understand it. I followed the steps on the function helper G:G,A:A,A2,B:B,B2 and I get this... how does the =G2 affecting formula?
=IF(MAXIFS(G:G,A:A,A2,B:B,B2)=G2,"Yes","No")

 

[Fluff]

how does the =G2 affecting formula

In exactly the same way as it does in your original formula.

 

[wizardmagu]

I see. In your MAXIFS statement, the G:G is the max_range I am wanting to find for that row. The criteria_range2 is just adding that second criteria for it to return then return the Yes or No

 

[Fluff]

That's right.

 

[wizardmagu]

Thanks again!

 

[Fluff]

You're welcome.