How to find the 2nd most frequently occurring text value in the dataset (without SQL query)-CodePudding

I have a question and need your help. Thanks in advance.

I can find the 2nd most frequently occurring text value via using SQL Query but I need to find it without SQL. (based on some conditions) / Answer 2 (new) sheet

Here is the Test Sheet

and try:

=INDEX(LAMBDA(y; FILTER(y; COUNTIFS(INDEX(y;;1); INDEX(y;;1); 
 SEQUENCE(COUNTA(INDEX(y;;1))); "<="&SEQUENCE(COUNTA(INDEX(y;;1))))=2))
 (SORT(SPLIT(FLATTEN(LAMBDA(x; INDEX(QUERY(x; "select Col2,Col3,Col4"; 1); 1)&"×"&
 INDEX(x;;1)&"×"&QUERY(x; "select Col2,Col3,Col4"; ))
 (QUERY({'Raw Data'!B2:B\'Raw Data'!E2:E}; 
 "select Col2,count(Col2) where not Col2 matches '^$|N/A' 
  group by Col2 pivot Col1"))); "×"); 1; 1; 3; 0));;2)

UPDATE - without SQL:

=BYROW(A7:A9; LAMBDA(y; TEXTJOIN(CHAR(10); 1; LAMBDA(z; FILTER(INDEX(z;;2); INDEX(z;;1)=y; INDEX(z;;3)=
 LARGE(INDEX(z;;3); 2  N("2 for 2nd largest"))))(LAMBDA(x; FILTER(x; INDEX(x;;1)=y))
 (LAMBDA(b; e; SORT(SORTN(SORT({B\ E\ COUNTIFS(E; E; 
 SEQUENCE(COUNTA(E)); "<="&SEQUENCE(COUNTA(E)))\ B&E}; 3; 0); 9^9; 2; 4; 1); 3; 0))
 (FILTER('Raw Data'!B:B; 'Raw Data'!D:D="Fail"; 'Raw Data'!E:E<>"N/A"; 'Raw Data'!B:B=y); 
  FILTER('Raw Data'!E:E; 'Raw Data'!D:D="Fail"; 'Raw Data'!E:E<>"N/A"; 'Raw Data'!B:B=y)))))))