excel公式如何返回满足条件的数组

Question

vlookup 只能返回第一个数，有办法可以返回满足这个条件的全部单元格的数组吗？vlookup不行，有其他函数吗？
例如：
c1 c2
1 a
2 c
1 d

求：c1为1的c2的值，返回数组{"a","d"}

Answer 1

写 VBA 函数

SplicingRange(Arr As Range, n As Integer)，第一个参数是选定区域，第二个参数是 你想查询的哪个值

结果

Function SplicingRange(Arr As Range, n As Integer)
    Dim x As Integer
     For i = 1 To Arr.Rows.Count
        If Arr.Cells(i, 1) = n Then
            If SplicingRange <> "" Then
               SplicingRange = SplicingRange & "," & Arr.Cells(i, 2)
            Else
                SplicingRange = Arr.Cells(i, 2)
            End If
        End If
     Next
      
End Function

插入开发工具，并启用宏

改为返回 数组

Function SplicingRange(Arr As Range, n As Integer)
    Dim x As Integer
     For i = 1 To Arr.Rows.Count
        If Arr.Cells(i, 1) = n Then
            If SplicingRange <> "" Then
               SplicingRange = SplicingRange & ",""" & Arr.Cells(i, 2) & """"
            Else
                SplicingRange = "{""" & Arr.Cells(i, 2) & """"
            End If
        End If
     Next
     SplicingRange = SplicingRange & "}"
      
End Function