「代码」代替VLOOKUP函数，用Application.Match怎么样？结果却大大出乎我的意料！

Sub myQuery()    Dim i As Long, j As Long    Dim ws As Worksheet, rng As Range    Dim lRow As Long, lCol As Integer    Dim arr(), currRow As Long, temp()    Dim t As Double    t = Timer    Set ws = ThisWorkbook.Sheets("职工档案")    With ws        lRow = .Cells(.Rows.Count, 1).End(xlUp).row        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column        arr = .Cells(1, 1).Resize(lRow, lCol).Value    End With    Set ws = ThisWorkbook.Sheets("查询")    With ws        lRow = .Cells(.Rows.Count, 1).End(xlUp).row        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column        Set rng = .Cells(1, 1).Resize(lRow, lCol)        temp = rng.Value    End With    For i = 2 To lRow            Debug.Print Timer - t
        For j = 2 To lCol            temp(i, j) = myMatch(arr, temp(i, 1), temp(1, j))        Next    Next    With rng        .Cells.NumberFormat = "@"        .Columns(3).NumberFormat = "0"        .Value = temp            End With    MsgBox "Done! Time consumed: " & Round(Timer - t, 2) & " seconds."    End Sub
Sub myQuery2()    Dim i As Long, j As Long    Dim ws As Worksheet, rng As Range    Dim lRow As Long, lCol As Integer    Dim arr(), currRow As Long, currCol As Long, temp()    Dim t As Double    t = Timer    Set ws = ThisWorkbook.Sheets("职工档案")    With ws        lRow = .Cells(.Rows.Count, 1).End(xlUp).row        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column        arr = .Cells(1, 1).Resize(lRow, lCol).Value    End With    Set ws = ThisWorkbook.Sheets("查询")    With ws        lRow = .Cells(.Rows.Count, 1).End(xlUp).row        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column        Set rng = .Cells(1, 1).Resize(lRow, lCol)        temp = rng.Value    End With    For i = 2 To lRow        currRow = Application.Match(temp(i, 1), Application.Index(arr, 0, 1), 0)        Debug.Print Timer - t        temp(i, 2) = arr(currRow, 2)        temp(i, 3) = arr(currRow, 6)        temp(i, 4) = arr(currRow, 9)        temp(i, 5) = arr(currRow, 11)    Next    With rng        .Cells.NumberFormat = "@"        .Columns(3).NumberFormat = "0"        .Value = temp    End With    MsgBox "Done! Time consumed: " & Round(Timer - t, 2) & " seconds."End Sub
Function myMatch(arr(), rowField, colField, Optional matchCol As Long = 1)    On Error Resume Next    Dim row(), col()    Dim iRow As Long, iCol As Long    row = Application.Index(arr, 0, matchCol)    col = Application.Index(arr, 1)    iRow = Application.Match(rowField, row, 0)    iCol = Application.Match(colField, col, 0)    myMatch = arr(iRow, iCol)End Function

Sub myQuery3()    '//采用字典    Dim i As Long, j As Long    Dim ws As Worksheet, rng As Range    Dim lRow As Long, lCol As Integer    Dim arr(), currRow As Long, currCol As Long, temp()    Dim dic As Object    Dim t As Double    Set dic = CreateObject("Scripting.Dictionary")    t = Timer    Set ws = ThisWorkbook.Sheets("职工档案")    With ws        lRow = .Cells(.Rows.Count, 1).End(xlUp).row        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column        arr = .Cells(1, 1).Resize(lRow, lCol).Value    End With        '把需要的数据装入字典    For i = 2 To lRow        dic(arr(i, 1)) = Array(arr(i, 2), arr(i, 6), arr(i, 9), arr(i, 11))    Next        Set ws = ThisWorkbook.Sheets("查询")    With ws        lRow = .Cells(.Rows.Count, 1).End(xlUp).row        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column        Set rng = .Cells(1, 1).Resize(lRow, lCol)        temp = rng.Value    End With    For i = 2 To lRow        arr = dic(temp(i, 1))        Debug.Print Timer - t        For j = 2 To lCol            temp(i, j) = arr(j - 2)        Next    Next    With rng        .Cells.NumberFormat = "@"        .Columns(3).NumberFormat = "0"        .Value = temp    End With    MsgBox "Done! Time consumed: " & Round(Timer - t, 2) & " seconds."End Sub