好吧，类似这样的工作，但使用了一个helper列：

unique（）的使用需要len（）的控制，以避免“N”通过。
Index（）和match（）是经典的。
最后一个是iferror（），match（）使用了两次，并与and（）结合使用。

您可以通过fail过滤sheet1（1），并通过sheet2中出现的同名fail过滤sheet1（2）：

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

或者使用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"))

这种方式使用ADODB查询工作表范围并将结果复制到目标范围（一个单元格）。该函数返回复制结果的范围。

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