Last date = YES/NO

[Pizzio]

I'm looking for a formula that will tell me IF(RED = Column G and IF 12/31/35> the last date for RED in column H. Any ideas?

Type	Date
Red	12/31/2023​
Blue	8/15/2025​
Red	12/31/2015​
Red	12/31/2028​
Black	6/30/2026​

 

[Skybluekid]

What version of Excel are you using?

Also is this meant to be a date?

 

[Pizzio]

What version of Excel are you using?

Also is this meant to be a date? View attachment 84478

MS 365. Yes, looking for if 12/31/35 is the last day for RED in column H.

 

[Skybluekid]

Try this, the 49674 is the serial number for 31/12/2035

Book1
	A	B	C	D	E	F
1
2		Type	Date
3		Red	31/12/2023
4		Blue	15/08/2025
5		Red	31/12/2015		TRUE
6		Red	31/12/2028		TRUE
7		Black	30/06/2026
8
Sheet1

Cell Formulas
Range	Formula
E3:E7	E3	=IFS(B3<>"Red","",49674>XLOOKUP("Red",$B$2:B2,$C$2:C2,"",0,-1),TRUE,TRUE,"")

 

[awoohaw]

if the target date to look for everchanges...

WorkBook1.xlsx
	B	C	D	E	F
2	Type	Date			only reds
3	Red	12/31/2023
4	Blue	8/15/2025		Is the Max
5	Red	12/31/2015
6	Red	12/31/2028		Is the Max	Is the Max
7	Black	6/30/2026		Is the Max
Sheet4

Cell Formulas
Range	Formula
E3:E7	E3	=IF(MAXIFS($C$3:$C$7,$B$3:$B$7,B3)=C3,"Is the Max","")
F3:F7	F3	=IF(MAXIFS($C$3:$C$7,$B$3:$B$7,B3,$B$3:$B$7,"Red")=C3,"Is the Max","")

 

[Peter_SSs]

MS 365.

Please put that in your Account details (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’)