在G2单元格中，可以指定以下公式

=IFERROR(INDEX(B:B,MATCH("*"&F2&"*",A:A,0)),"")

在H2列中，您可以指定以下公式

=IFERROR(INDEX(C:C,MATCH("*"&F2&"*",A:A,0)),"")

然后将这些公式复制到下面的单元格中
如果相反，则在单元格B2中输入以下公式

=IFERROR(INDEX(G:G,SUMPRODUCT((--ISNUMBER(SEARCH(F:F,A2)))*(F:F<>""),ROW(F:F))),"")

并在C2中输入以下公式

=IFERROR(INDEX(H:H,SUMPRODUCT((--ISNUMBER(SEARCH(F:F,A2)))*(F:F<>""),ROW(F:F))),"")

现在把公式复制到B和C下面的单元格中。

使用SUMPRODUCT可以获得行号，然后输出该值：

一级：

=IF(SUMPRODUCT(--((LEN($A$2:$A$7)-LEN(SUBSTITUTE($A$2:$A$7,F2,"")))>0))=0,"NoL1",INDEX($B$2:$B$7,SUMPRODUCT(--((LEN($A$2:$A$7)-LEN(SUBSTITUTE ($A$2:$A$7,F2,"")))>0)*ROW($A$2:$A$7))-1))

二级：

=IF(SUMPRODUCT(--((LEN($A$2:$A$7)-LEN(SUBSTITUTE($A$2:$A$7,F2,"")))>0))=0,"NoL2",INDEX($C$2:$C$7,SUMPRODUCT(--((LEN($A$2:$A$7)-LEN(SUBSTITUTE ($A$2:$A$7,F2,"")))>0)*ROW($A$2:$A$7))-1))

我已经上传了一个样本到Gdrive，这样你就可以检查公式：
https://docs.google.com/spreadsheets/d/1UWfVkHUuVn88KVF9sJDvOl0fF4iQddZr/edit?usp=sharing&ouid=114417674018837700466&rtpof=true&sd=true
工作原理：该公式基于不同的部分工作。首先，因为我们使用SUMPRODUCT，所以我们同时处理多个范围。默认情况下，Excel中的常见函数通常需要单个单元格作为参数，但在这里我们同时使用多个单元格。现在它是如何工作的：

1. (LEN($A$2:$A$7)-LEN(SUBSTITUTE($A$2:$A$7,F2,"")))将计算字符串的原始长度和替换为搜索词后的新长度，如果该单元格中没有内容，则差值为0，否则大于cero，例如：当搜索词是AA时，这部分将是{12,12,12,12,14,20}-{10,12,12,12,14,20}，并返回数组{2,0,0,0,0,0}。这意味着第一个单元格包含搜索词，因为它是唯一的非零值。
2. --(previous formula)>0)现在double unary operator将检查前一个数组是否大于零，返回1或0，因此{2,0,0,0,0,0}现在是{1,0,0,0,0,0}
   1.第1步和第2步被 Package 在SUMPRODUCT中，SUMPRODUCT将汇总前面数组中的所有内容，即SUMPRODUCT({1,0,0,0,0,0}) = 1+0+0+0+0+0 = 1。
   1.前面的所有步骤都被封装在一个IF函数中。如果前面的结果等于0，则返回NoL1，因为没有匹配，否则继续计算。
   1.现在，该公式再次重复步骤1和2，并再次获得如下所示的数组：{1,0,0,0,0,0}.
   1.请注意，由于我们添加了ROW($A$2:$A$7)，所以在第二个SUMPRODUCT中还有一个额外的计算，这将创建一个包含所有行号的数组，输出为{2,3,4,5,6,7}。
   1.使用星号*将步骤5中的数组乘以步骤6中的数组，如下所示：{1,0,0,0,0,0} * {2,3,4,5,6,7} = {2,0,0,0,0,0}，这一点很关键，因为这里返回的唯一数字是行号，其中包含包含搜索单词!!!的注解。
   1.同样，步骤7位于SUMPRODUCT内部，因此它将对所有值求和，输出为2
   1.第8步的输出是INDEX函数中返回所需注解的第二个参数。请注意，我们执行-1是因为ROW返回相对于整个工作表的实际行号，但由于第一行数据是第2行而不是第1行，因此必须执行-1进行调整。
   1.最后，所有内容都变成了一个简单的索引，如下所示：INDEX($C$2:$C$7,2-1)，这将返回预期输出。
   级别2的公式完全相同，只是在INDEX部分中提取数据的位置发生了变化，并返回inf NoL2为不匹配。
   我强烈建议您使用工具公式求值，这样您就可以一步一步地看到Excel是如何进行每个计算的。您会更好地理解。
   Evaluate a nested formula one step at a time
   您必须阅读和了解更多有信誉的来源：
   SUMPRODUCT
   INDEX
   ROW
   IF
   LEN
   SUBSTITUTE

除Excel公式外，Excel VBA自定义项也可在此使用。

• 此解决方案是为那些想要/需要/可以使用自己的VBA代码的人提供的建议。*

Option Explicit
' Option Compare Text ' That is, "AAA" is equal to "aaa".
' Option Compare Binary ' That is, "AAA" is less than "aaa".

Public Function SearchNotes( _
    note As Range, _
    criteria As Range, _
    no1 As String, _
    no2 As String)
    
    On Error GoTo err_search

    Dim k As Variant
    Dim d As Object
    
    Set d = Map(criteria)
    
    For Each k In d.Keys()
        If Contains(note, k) Then
            SearchNotes = Array(d(k).Cells(2).Value, d(k).Cells(3).Value)
            Exit Function
        End If
    Next

    SearchNotes = Array(no1, no2)
    
    Exit Function
    
err_search:
    ' Debug.Print "Error occured: '" & Err.Description & "'. Error number: '" & Err.Number & "'.", vbCritical, "Error"
    SearchNotes = CVErr(xlErrValue)
End Function

Private Function Contains(note As Range, k As Variant) As Boolean
    Contains = IIf(VBA.Strings.InStr(VBA.CStr(note.Value), VBA.CStr(k)) > 0, _
        True, _
        False)
End Function

Private Function Map(criteria As Range) As Object
    Dim d As Object
    Dim r As Range
    Dim k As String
    
    Set d = VBA.Interaction.CreateObject("Scripting.Dictionary")
    
    For Each r In criteria.Rows
        k = r.Cells(1).Value
        If Not d.Exists(k) Then
            d.Add Key:=k, Item:=r
        End If
    Next
    Set Map = d
End Function

如何在Excel中调用该自定义项

• • 重要信息**：选择两个单元格，因为公式将返回两个值的数组

点击Ctrl + Shift + Enter，因为UDF必须用作数组公式

最后，只需将单元格B2：C2中的公式复制到其他行