3
\$\begingroup\$

I am sharing this function I wrote with the community to provide an alternative to slow VLOOKUP and/or Index-Match functions. Also, if you have any feedback/suggestions to make this better please let me know. I tested this function using a lookup of just under 160,000 rows and it returned results in 2 seconds. This surpasses the native VLOOKUP and or Index-Match by a very large margin.

Usage:

  1. Can be used in a worksheet as an array formula (i.e. must be entered using Ctrl+Shift+Enter).

  2. Can be used in VBA as well.

Original Post:

 Public Function FastLookUp(ByRef rngLookUpVals As Range, ByRef rngLookUpTable As Range, _
                               ByVal lngLookUpValCol As Long, ByVal lngSearchCol As Long, _
                               ByVal lngReturnCol As Long, _
                               Optional ByVal boolBinaryCompare As Boolean = True) As Variant

  Dim i As Long
  Dim dictLooUpTblData As Object
  Dim varKey As Variant
  Dim arryLookUpVals() As Variant, arryLookUpTable() As Variant, _ 
         arryOut() As Variant

        arryLookUpVals() = rngLookUpVals.Value2
        arryLookUpTable() = rngLookUpTable.Value2

        Set dictLooUpTblData = CreateObject("Scripting.Dictionary")
        If boolBinaryCompare Then 
            dictLooUpTblData.CompareMode = vbBinaryCompare 
        Else 
            dictLooUpTblData.CompareMode = vbTextCompare
        End If 

        'add lookup table's lookup column to 
        'dictionary 
        For i = LBound(arryLookUpTable, 1) To UBound(arryLookUpTable, 1)

            varKey = Trim(arryLookUpTable(i, lngSearchCol))

            If Not dictLooUpTblData.Exists(varKey) Then
                'this is called a silent add with is faster
                'than the standard dictionary.Add Key,Item 
                'method
                dictLooUpTblData(varKey) = arryLookUpTable(i, lngReturnCol)
            End If

            varKey = Empty
        Next i

        i = 0: varKey = Empty 

        ReDim arryOut(1 To UBound(arryLookUpVals, 1), 1 To 1)

        For i = LBound(arryLookUpVals, 1) To UBound(arryLookUpVals, 1)
            varKey = Trim(arryLookUpVals(i, lngLookUpValCol))

            'if the lookup value exists in the dictionary 
            'at this index of the array, then return  
            'its correspoding item
            If dictLooUpTblData.Exists(varKey) Then 
                arryOut(i, 1) = dictLooUpTblData.Item(varKey)
            End If 

            varKey = Empty
        Next i

    FastLookUp = arryOut

End Function

Note: You could change the parameters to arrays instead of ranges and use this function to execute lookups on arrays in memory as well. You could also use Early-Binding for another increase in speed.

\$\endgroup\$
  • \$\begingroup\$ How large a margin? I ask because I think you can quantify this, and indeed identify any areas of your code that can be improved, with some profiling. Worth noting also that built in formulae can leverage Excel's multithreading capabilities IIRC, while VBA cannot (easily), so I'd be interested to see how your approach compares over large numbers of calls, as well as large search ranges. \$\endgroup\$ – Greedo Jun 15 at 13:21
  • 1
    \$\begingroup\$ @Greedo I ran several tests a using a VLOOKUP (exact match = True) on 158,000 rows, and compared the times to my function. It wasn’t even a contest. VLOOKUP to around 2 minutes while the function above took 2 seconds. As for large numbers of calls to the function, I plan to run some formal unit test and post my results. \$\endgroup\$ – rickmanalexander Jun 16 at 20:01
  • \$\begingroup\$ If you can sort the data first, you can probably speed this up more by using binary search. See: fastexcel.wordpress.com/2012/03/29/…. This approach would be nlogn complexity I believe. If you are looking for optimization on this method, you could use the built in .Items method on the dictionary to return an array of values. \$\endgroup\$ – Ryan Wildry Jun 17 at 12:28
  • \$\begingroup\$ @RyanWildry If you mean sorting to perform a binary search via the standard VLOOKUP, then I agree. However, for the FastLookUp function, the lookup to the dictionary is O(1) (constant time), so a b-tree built from sorting the dictionary by key would be unnecessary overhead. Though, the FastLookUp is n^2' where n` = number of rows. It annoys me that I have to loop twice.....(right I after I wrote this line a realized something, check my edit for more). \$\endgroup\$ – rickmanalexander Jun 17 at 15:36
  • \$\begingroup\$ @RyanWildry false alarm on checking my edit. I realized that my "realization" was useless, lol. \$\endgroup\$ – rickmanalexander Jun 17 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.