excel - 具有多个条件的 Vlookup，返回的数组插入到 ComboBox

Question

有没有办法将 VBA 用于具有多个条件的 Vlookup，然后将这些多个返回值插入到 ComboBox 中？对于下表，我希望将“Marlins”作为搜索条件，然后将 {RBI, Score, ABV} 作为选项插入到 ComboBox 中。这可能吗？
示例数据:


标识符
值(value)


海盗
分数

马林鱼
打点

马林鱼
分数

马林鱼
ABV

海盗
人力资源服务

Application.WorksheetFunction.VLookup("Marlins", Worksheets("Metadata").Range("A2:B5"), 2, False)

Answer 1

填充组合框
一个简单的例子

Sub PopulateComboBox()
    
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Metadata")
    
    ' Write the values of the range to a 2D one-based array.
    Dim sData As Variant: sData = ws.Range("A2:B5").Value
    
    ' Write the unique values from column 2, where column 1 is "Marlin",
    ' to the keys of a dictionary.
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare ' case-insensitive
    Dim r As Long
    For r = 1 To UBound(sData, 1)
        If StrComp(CStr(sData(r, 1)), "Marlin", vbTextCompare) = 0 Then
           dict(sData(r, 2)) = Empty
        End If
    Next r
    
    ' Write the values from the keys of the dictionary to the combo box.
    With ws.ComboBox1 ' active-x combobox on the worksheet
        .Clear
        If dict.Count > 0 Then .List = dict.Keys
    End With
        
End Sub