VBA Arrays - Faster alternative than Vlookup?

[danmcdon]

Hi! I have several multi-dimensional arrays from which I am using different lookup values to pull data. I'm currently using WorksheetFunction.Vlookup which I think is going very slow. I have many iterations and several different arrays so the total impact in run time is pretty significant. What is the best way to lookup values in an array? Or any other strategies to speed things up? THANKS!!

------------------

'example

Dim ArrData

ArrData = range("data")

Value = Application.WorksheetFunction.Vlookup(LookupValue, ArrData, 5, 0)

[Tinbendr]

I don't really have a answer for you, just opinions.

1. Unless you have other needs from your array's, I would skip them completely and use Range.Find.

2. Using brute force may be faster than Vlookup.

3. You can use binary search.

[danmcdon]

Thanks for your opinions

How would Range.Find work with a multidimensional array? What does it return? I tried:

pos = Range("data").Find(LookupValue, LookIn:=xlValues, LookAt=xlWhole)

The result was a type mismatch error

[p45cal]

How would Range.Find work with a multidimensional array? What does it return? I tried:

pos = Range("data").Find(LookupValue, LookIn:=xlValues, LookAt=xlWhole)

The result was a type mismatch error

If you Dimmed pos, Dim it as Range and use:
[vba]set pos = Range("data").Find(LookupValue, LookIn:=xlValues, LookAt:=xlWhole)[/vba] (note the added missing colon too), or if you're only searching in column 1 of the range then
[vba]set pos = Range("data").columns(1).Find(LookupValue, LookIn:=xlValues, LookAt:=xlWhole)[/vba] .Find returns a range object, so if not found pos will be nothing, so use the likes of:[vba]if not pos is nothing then myValue = pos.offset(,4).value[/vba] or you can use the .row property of pos to return the absolute row on the sheet.

[Bob Phillips]

Have you tried sorting the data before running VLookup?

[snb]

this will do:
[vba]
on error resume next
msgbox Range("data").Find(LookupValue, ,xlValues, 1).address
[/vba]

To look in the third column of an array and returning the corresponding value in column 5:

[vba]
Sub snb_002()
sn = Cells(1).CurrentRegion
c01 = sn(Application.Match("cc7", Application.Index(sn, , 3),0), 5)
End Sub
[/vba]