find oldest value

[JOEE1979]

How would I be able to write a formula to get oldest "FAIL"?

HOS.xlsm
	P	Q	R	S	T	U	V	W
5	12-Apr-24	19-Apr-24	26-Apr-24	3-May-24	10-May-24	17-May-24	24-May-24	oldest
6	FAIL	FAIL			FAIL
7					FAIL
8
9					FAIL
10					FAIL
11	FAIL	FAIL	FAIL	FAIL	FAIL
12	FAIL	FAIL	FAIL	FAIL	FAIL
Sheet1

Cell Formulas
Range	Formula
P5:V5	P5	=E5
P6:V12	P6	=IF(AND($A$1>E$14,E6=0),"FAIL","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P6:V12	Expression	=AND(P6="FAIL",P$14<(TODAY()-60))	text	NO
P6:V12	Expression	=AND(P6="FAIL",P$14<(TODAY()-30))	text	NO
P6:V12	Expression	=AND(P6="FAIL",P$14>=(TODAY()-30))	text	NO
P5:V5,P14:V14,P26:V26,P83:V83,P102:V102,P135:V135,P145:V145	Expression	=($A$1>P5)	text	NO

 

[hagia_sofia]

Do you mean something like =BYROW(P2:V8,LAMBDA(a,XLOOKUP("FAIL",a,$P$5:$V$11)))?

 

[JOEE1979]

Do you mean something like =BYROW(P2:V8,LAMBDA(a,XLOOKUP("FAIL",a,$P$5:$V$11)))?

I'd like it per row

 

[JOEE1979]

Do you mean something like =BYROW(P2:V8,LAMBDA(a,XLOOKUP("FAIL",a,$P$5:$V$11)))?

I figured it out,, just had to change cell numbers,,,, thanks,

 

[hagia_sofia]

The formula above should spill down for all rows within an array once you enter it into W5...

 

[JOEE1979]

The formula above should spill down for all rows within an array once you enter it into W5...

What if instead of having a function, I have a mane manager "qq"?
and I would like to find the oldest number greater than "0"

HOS.xlsm
	E	F	G	H	I	J	K	L
5	12-Apr-24	19-Apr-24	26-Apr-24	3-May-24	10-May-24	17-May-24	24-May-24	Oldest
6	0	0	6	6	0	0	0	#N/A
7	44	44	44	6	0	0	0
Sheet1

Cell Formulas
Range	Formula
E6:K7	E6	=qq
L6	L6	=BYROW(E6:K6,LAMBDA(a,XLOOKUP("6",a,E5:K5)))

 

[hagia_sofia]

If it is true that 6 is the only value you are looking for, then just replace E6:K6 by qq - or are there any additional conditions?

 

[JOEE1979]

If it is true that 6 is the only value you are looking for, then just replace E6:K6 by qq - or are there any additional conditions?

sometimes I have "6" and "44"

 

[hagia_sofia]

But which criteria are decisive for identifying the oldest date? I.e. is it if value = 6, or if value is not 0, or if value is < than or something else?

 

[JOEE1979]

If it is true that 6 is the only value you are looking for, then just replace E6:K6 by qq - or are there any additional conditions?

I simplified it and stayed with the 6