VBA查询：使用匹配和索引（后期绑定）

• 在每个模块的顶部使用Option Explicit，它将在编译时检测错误。在代码运行之前，例如它会提醒你有一个错字：Worksheet.Function应该是WorksheetFunction。
  快速修复
• 你可以摆脱像下面这样的东西，但我不会推荐它，即。如果没有匹配，则将发生错误。

Sub MysubQF()

    Dim X As Variant ' could be anything
    Dim Y As Double ' decimal number
    Dim Z As Long ' whole number
    
    Y = 22.5
    Z = 3
    
    With Workbooks("My file.xlsm").Worksheets("My sheet")
        X = WorksheetFunction.Index( _
                WorksheetFunction.Choose( _
                    Z, .Range("C1:C201"), .Range("B1:B201"), .Range("D1:D201")), _
                WorksheetFunction.Match(Y, .Range("A1:A201"), 0))
    End With
    
    MsgBox X

End Sub

研究

• 合理地使用常量（工作簿和工作表名称除外）并观察代码的行为。

Option Explicit

Sub Mysub()

    Const SRC_WORKBOOK As String = "My file.xlsm"
    Const SRC_WORKSHEET As String = "My sheet"
    Const SRC_LOOKUP_FIRST_CELL As String = "A2"
    Const SRC_LOOKUP_VALUE As Double = 22.5
    
    Const SRC_RETURN_COLUMNS As String = "B,C,D"
    Const SRC_RETURN_COLUMN_INDEX As Long = 3
    
    Dim swb As Workbook: Set swb = Workbooks(SRC_WORKBOOK)
    ' If this code is in the same workbook, you can instead simply use:
    'Dim swb As Workbook: Set swb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = swb.Sheets(SRC_WORKSHEET)
    
    Dim slrg As Range, srCount As Long
    
    With sws.Range(SRC_LOOKUP_FIRST_CELL)
        srCount = sws.Cells(sws.Rows.Count, .Column).End(xlUp).Row - .Row + 1
        If srCount < 1 Then Exit Sub ' no data
        Set slrg = .Resize(srCount)
    End With
    
    Dim srCols() As String: srCols = Split(SRC_RETURN_COLUMNS, ",")
    
    If UBound(srCols) + 1 < SRC_RETURN_COLUMN_INDEX Then
        MsgBox "Cannot index column " & SRC_RETURN_COLUMN_INDEX & ". " _
            & "There are only " & UBound(srCols) + 1 & " columns.", vbCritical
        Exit Sub
    End If
    
    Dim srCol As String:
    srCol = Application.Index(srCols, SRC_RETURN_COLUMN_INDEX)
    
    Dim srIndex: srIndex = Application.Match(SRC_LOOKUP_VALUE, slrg, 0)
    
    If IsError(srIndex) Then
        MsgBox "The value " & SRC_LOOKUP_VALUE & " was not found " _
            & "in column """ & srCol & """.", vbCritical
        Exit Sub
    End If
    
    Dim srrg As Range: Set srrg = slrg.EntireRow.Columns(srCol)
    Dim srCell As Range: Set srCell = srrg.Cells(srIndex)
    
    Dim Result As Variant: Result = srCell.Value

    MsgBox "The result is """ & Result & """. It was found in cell """ _
        & srCell.Address(0, 0) & """.", vbInformation

End Sub

Range("A1:A201").Value尝试从200个单元格中获取一个值，而不是200个不同的值。例如，尝试简单的?Range("A1:A2").Value，看到相同的错误。
使用Range("A1:A201")等。适用于您的所有范围
另一个你可能还没有注意到的问题：22.5不是Long。如果你想使用小数，Dim Y As Double。否则，宏将工作，但它会给予你一个不正确的结果。

我改变了“激活”与直接引用的书和表。在Choose中，你有一个.Value，而你必须返回一个Range。为了使这一点显而易见，并使代码易于阅读，我将Choose移出了查找。最后，Lookup返回的值应该在某个地方使用，所以我创建了Sub函数并返回找到的值。

Public Function MyFunc() As Long
   Dim Y As Long, Z As Integer, ws As Worksheet, r As Range
   
   Y = 22.5
   Z = 3
   
   Set ws = Workbooks("My file.xlsm").Worksheets("My sheet")
   Set r = WorksheetFunction.Choose(Z, ws.Range("C1:C201"), ws.Range("B1:B201"), ws.Range("D1:D201"))
   MyFunc = WorksheetFunction.Lookup(Y, ws.Range("A1:A201").Value, r)
End Function