Excel VBA：如何通过两个窗体变量匹配数据行并返回对应值

问题背景 #

有一张包含49750行数据的工作表（Sheet1），需要通过VBA窗体输入的两个变量（中队Sq.Value、呼号CallSn.Value），匹配对应行的飞行员值。原工作表公式=INDEX(Col3, MATCH(var1 & var2, Col1 & Col2, 0))可实现该功能，但直接移植到VBA时，因VBA无法像工作表公式那样拼接两个列范围，导致报错。

示例数据：

当前报错代码：

rw = WorksheetFunction.Match(Sq.Value & CallSn.Value, Worksheets(Sheet1).Range("A:A") & Worksheets(Sheet1).Range("B:B"), 0)
Pilot= Worksheets(Sheet1).Cells(rw, 3)

解决方案 #

方案1：用Evaluate执行数组公式逻辑

通过VBA的Evaluate方法模拟工作表数组公式的逻辑，代码简洁，适合单次查询：

Dim pilot As String
Dim lastRow As Long
Dim matchFormula As String

' 获取数据最后一行，避免整列引用提升效率
lastRow = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Row

' 构建匹配公式字符串，用动态范围替代整列
matchFormula = "=INDEX(Sheet1!C1:C" & lastRow & ", MATCH(""" & Sq.Value & """&""" & CallSn.Value & """, Sheet1!A1:A" & lastRow & "&Sheet1!B1:B" & lastRow & ", 0))"
pilot = Evaluate(matchFormula)

方案2：用Dictionary建立快速查询映射

若需多次查询，先将数据加载到Dictionary中，后续查询直接按键取值，效率极高：

Dim pilotDict As Object
Dim lastRow As Long
Dim i As Long
Dim targetKey As String
Dim pilot As String

Set pilotDict = CreateObject("Scripting.Dictionary")
lastRow = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Row

' 遍历数据，将"中队|呼号"作为键，飞行员值作为项
For i = 2 To lastRow ' 假设第1行是表头
    pilotDict(Worksheets("Sheet1").Cells(i, "A").Value & "|" & Worksheets("Sheet1").Cells(i, "B").Value) = _
        Worksheets("Sheet1").Cells(i, "C").Value
Next i

' 查询目标值
targetKey = Sq.Value & "|" & CallSn.Value
If pilotDict.Exists(targetKey) Then
    pilot = pilotDict(targetKey)
Else
    pilot = "未找到匹配项" ' 处理无匹配的情况
End If

Set pilotDict = Nothing ' 释放对象

注：用|作为分隔符避免拼接冲突，比如中队"RedF"+呼号"ive"不会和"Red"+"Five"混淆。

方案3：临时辅助列+Find方法

通过临时添加辅助列拼接条件，再用Find查找，适合临时场景：

Dim lastRow As Long
Dim helperCol As Range
Dim matchCell As Range
Dim pilot As String

lastRow = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Row
Set helperCol = Worksheets("Sheet1").Columns("D") ' 选择空列作为辅助列

' 拼接A、B列内容到辅助列
helperCol.Formula = "=A1&B1"
helperCol.Value = helperCol.Value ' 转为静态值

' 查找目标拼接值
Set matchCell = helperCol.Find(Sq.Value & CallSn.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not matchCell Is Nothing Then
    pilot = Worksheets("Sheet1").Cells(matchCell.Row, "C").Value
Else
    pilot = "未找到匹配项"
End If

helperCol.Clear ' 清理辅助列

内容的提问来源于stack exchange，提问作者Keesler Water Walker