# Alternative for VLOOKUP/Index-Match in VBA

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
'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.

• 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. – Greedo Jun 15 at 13:21
• @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. – rickmanalexander Jun 16 at 20:01
• 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. – Ryan Wildry Jun 17 at 12:28
• @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). – rickmanalexander Jun 17 at 15:36
• @RyanWildry false alarm on checking my edit. I realized that my "realization" was useless, lol. – rickmanalexander Jun 17 at 16:01