Excel VBA Result based on Multiple Column & Conditions

[bobbyexcel]

Need some help on the below requirement. Please help me..

I want to update the Column E (Final Status) based on below Condition..
1. Col A & Col C should be equal
2. Col E should have Success / Failed ( don't consider any text other than Success or Failed)
3. if above 2 qualifies then consider the highest value (or nearest to 1) Status(col B) to be applied to all the cells in Col E for the matched cells (from point 1 & 2 above)

Then the final result should come as..

 

[bobbyexcel]

@Mackeral Thanks for your continuous track on this question.. I've modified my code now but getting the below error (I've actually created a new post for my query..).. could you please help me on this..

type mismatch error on Array date format

Can someone help me on this error please..

www.mrexcel.com

 

[BSALV]

as you have 365, even those formules predict 5 "Success"

bobbyExcel.xlsb
	A	B	C	D	E	F
1	JOB	STATUS	START	END	CALCUL	Final2
2	XYZ	FAILED	05-apr-22	05-apr-22	0,0597	SUCCESS
3	XYZ	SUCCESS	05-apr-22	06-apr-22	0,7503	SUCCESS
4	ABC	FAILED	05-apr-22	05-apr-22	0,0597	SUCCESS
5	ABC	SUCCESS	05-apr-22	06-apr-22	0,7503	SUCCESS
6	ABC	SUCCESS	05-apr-22	06-apr-22	0,625	SUCCESS
Jobs

Cell Formulas
Range	Formula
F2:F6	F2	=INDEX(SORT(FILTER(TBL_Bobby2[[JOB]:[CALCUL]],([JOB]=[@JOB])*([START]=[@START])*(([STATUS]="failed")+([STATUS]="success"))),5,-1),1,2)

 

[bobbyexcel]

Thanks for your reply @BSALV .. I've modified your script based on my requirements however I'm getting below error now.. Could you please help on this type mismatch error.

 

[bobbyexcel]

@BSALV could you please help me on this ??