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

Question

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 https://docs.google.com/spreadsheets/d/14aW9OMWh6AbS0Y05LKEg9L6P2uT9elxD/edit#gid=2144114460

PS: You can Edit the sheets.

Answer 1

=MIN('Answer 1'!$F$2:$F$15)

equals to 20 and only Hans got that

what you need is:

=INDEX(IFNA(VLOOKUP(A2:A5; 
 SORT({'Answer 1'!B2:B15\ 'Answer 1'!A2:A15\ 'Answer 1'!F2:F15}; 3; 1); {2\ 3}; 0)))

---

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)))))))