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


"Frank & Pam Hayes" wrote in message
news:KBlKe.9141$0d.3629@trnddc02...
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