Using an Array instead of a Vlookup to improve speed

[Frank & Pam Hayes]

I have an two dimensional array (MyArray) that describes 5000 invoices.

MyArray(i, 1) is the PRODUCT
MyArray(i, 2) is the VOLUME
MyArray(i, 3) is the REVENUE
MyArray(i, 4) is the TYPE

I am using Application.Vlookup to lookup the PRODUCT in a named range on a
worksheet to see if this is a product I sold in the previous period. If the
Vlookup is successful, I put "Existing Product" in MyArray(i, 4). If the
vlookup returns an error (using FALSE as the last argument), then I put "New
Product" in MyArray(i, 4).

The Application.Vlookup is a bit slow. Can someone point me to a VBA
example where the author wrote the target range into a second Array and then
used a For Next loop to perform the same basic logic as the vlookup would?

Thanks,

Frank Hayes

 

[Niek Otten]

That would probably a lot slower.
VLOOKUP is slow when the 4th argument is set to FALSE.
Use a sorted range and set the argument tot TRUE (default); this requires
that you check to see if the item found is the correct one. Because you now
probably need 2 (or even more) VLOOKUPS, you could use MATCH instead, and
use INDEX to retrieve the items from the table.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

 

[Tom Ogilvy]

Dim v as Variant
Dim j as Long, i as Long
j = some index number

v = Range("Table").value
for i = l to ubound(v,1)
if v(i,1) = MyArray(j,1) then
MyArray(j,4) = "Existing Product"
exit for
end if
Next
if isemty(MyArray(j,4)) then
MyArray(j,4) = "New Product"
end if

 

[Tom Ogilvy]

I agree with Nick as a general impression. That said, if you want to
examine the array approach, I would organize the code so you only pick up
the named range one time and hold it in a global/public variable. (rather
than pick it up each time in a loop which might be implied in my sample if
you are looping on j as well).

 

[RB Smissaert]

It will depend on the particular situation, but I think doing a lookup via
an array loop is much faster.
This is an example of an array function doing a lookup, that you can adapt
to suit your situation.

Function ArrayLookup(ByVal lLookupValue As Long, _
ByRef arr As Variant, _
ByRef lCol1 As Long, _
ByRef lCol2 As Long, _
Optional bReplace As Boolean = False, _
Optional ByRef strReplace As String = "any", _
Optional ByRef strReplace2 As String = "") As String

'will do a lookup in a 2-D array
'the lookup value has to be an integer number
'the result will have to be a string value
'optionally will replace a found value with another string
'---------------------------------------------------------
Dim LB As Byte
Dim UB As Long
Dim i As Long

LB = LBound(arr)
UB = UBound(arr)

'to correct for 0-based arrays
'-----------------------------
lCol1 = lCol1 + (LB - 1)
lCol2 = lCol2 + (LB - 1)

For i = LB To UB
If arr(i, lCol1) = lLookupValue Then
ArrayLookup = arr(i, lCol2)
If bReplace Then
If ArrayLookup = strReplace Then
ArrayLookup = strReplace2
End If
End If
Exit Function
End If
Next

'for in case no match was found, pass the supplied value back
'------------------------------------------------------------
ArrayLookup = lLookupValue

End Function


RBS