find students who failed each exam from two different sheets

I am trying to find students who failed each exam from different sheets and write surname and name to new sheet3

sheet1 EN

sheet2 МА

sheet3 expected result

Well, something like this works, but used a helper column:

The use of unique() needs the control of len() to avoid the "N" coming through.

Index() with match() is classic.

Last is iferror() with match() used twice and combined with and().

You could filter sheet1 (1) by fail and (2) by a fail with the same name occurring in sheet2:

=FILTER(Sheet1!B2:D5,(Sheet1!D2:D5="F")*
ISNUMBER(XMATCH(Sheet1!B2:B5&"|"&Sheet1!C2:C5,
FILTER(Sheet2!B2:B4&"|"&Sheet2!C2:C4,Sheet2!D2:D4="F"))))

or using xlookup:

=FILTER(Sheet1!B2:D5,(Sheet1!D2:D5="F")*
(IFERROR(XLOOKUP(Sheet1!B2:B5&"|"&Sheet1!C2:C5,
Sheet2!B2:B4&"|"&Sheet2!C2:C4,Sheet2!D2:D4),"")="F"))

This way uses ADODB to query the sheet ranges and copy the result at destination range (one cell). The function returns the range where copied the results.

    Option Explicit
    '---------------------------------------------------------------
    ' ra As Range> first range of data with headers
    ' rb As Range> first range of data with headers
    ' destination As Range> one cell anywhere to copy the results
    '---------------------------------------------------------------
    Public Function sqLFailedTwice(ra As Range, rb As Range, destination As Range) As Range
       Dim connection As Object, recSet As Object, sql As String, tblA As String, tblB As String
       Const PRO = " [", META = "] "
       
       tblA = PRO & ra.Worksheet.Name & "$" & ra.Address(0, 0) & META
       tblB = PRO & rb.Worksheet.Name & "$" & rb.Address(0, 0) & META
       
       sql = "SELECT A.[S], A.[N], ""failed"" AS failed FROM " & tblA & "AS A INNER JOIN " & tblB & _
             "AS B ON A.[S]=B.[S] AND A.[N]=B.[N] WHERE A.Grade=""F"" AND B.Grade=""F"" ORDER BY A.[S],A.[N]"
    
       Set connection = CreateObject("ADODB.Connection")
       With connection
          .CursorLocation = 3
          .Provider = "Microsoft.ACE.OLEDB.12.0"
          .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
          "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
          .Open
          Set recSet = .Execute(sql)
       End With
       'if found records copy them and return the range where copied
       If recSet.recordCount > 0 Then
          Call destination.CopyFromRecordset(recSet)
          Set sqLFailedTwice = destination.Resize(recSet.recordCount, 3)
       End If
       recSet.Close
       connection.Close
       Set recSet = Nothing
       Set connection = Nothing
    End Function
    
    
    Sub execSql()
       Dim rslt As Range
       'WE CAN READ FROM ANY SHEET/RANGE AND COPY TO ANY SHEET/RANGE
       Set rslt = sqLFailedTwice(SHEET08.Range("P1:S5"), _
                                 SHEET09.Range("P1:S4"), _
                                 SHEET10.Range("G2"))
       Debug.Print rslt.Address
    End Sub