Option Explicit
Const strWorkbook As String = "C:\Path\WorkbookName.xlsx"        'The path of the workbook
Const strRange As String = "RangeName"

Private Function xlFillArray(strWorkbook As String, _
                             strRangeName As String) As Variant
Dim RS As Object
Dim CN As Object
Dim iRows As Long
    Set CN = CreateObject("ADODB.Connection")
    CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                              "Data Source=" & strWorkbook & ";" & _
                              "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

    Set RS = CreateObject("ADODB.Recordset")
    RS.Open "SELECT * FROM [" & strRangeName & "]", CN, 2, 1

    With RS
        .MoveLast
        iRows = .RecordCount
        .MoveFirst
    End With
    xlFillArray = RS.GetRows(iRows)
    If RS.State = 1 Then RS.Close
    Set RS = Nothing
    If CN.State = 1 Then CN.Close
    Set CN = Nothing
lbl_Exit:
    Exit Function
End Function

Sub Test()
'Look for the value 'Al Gore' in the first column of the array
'And report the value in the second column
'Which here should report - 14132
Dim arr() As Variant
    arr = xlFillArray(strWorkbook, strRange)
    On Error GoTo lbl_Exit
    For iRows = 0 To UBound(arr, 1)
        sResult = arr(0, iRows)
        If sResult = "Al Gore" Then
            MsgBox arr(1, iRows)
        End If
    Next iRows
lbl_Exit:
    Exit Sub
End Sub