Generally, to do an array formula you need to use evaluate:

Sub TestVlook()
Dim varr As Variant
Dim i As Long
varr = Evaluate("VLookup(10035,A1:E5,{2, 3, 4, 5}, False)")
If IsArray(varr) Then
For i = LBound(varr) To UBound(varr)
Debug.Print i, varr(i)
Next
End If
End Sub

Regards,
Tom Ogilvy


Alan Beban wrote in message
...
Rob Bovey, Dave Peterson and Dana DeLouis recently provided helpful
suggestions for a similar situation with COUNTIF. None of them seems to
resolve the following superficially similar problem with VLOOKUP.

=VLOOKUP(10035,A1:E5,{2,3,4,5},FALSE)

works fine on the worksheet.

With the VLookups function from my website, either of the following VBA
code snippets works as well:

arr = VLookups(10035, Range("tbl2"),Application.Evaluate("{2,3,4,5}"))

arr = VLookups(10035, Range("tbl2"), Evaluate("{2,3,4,5}"))

But notwithstanding the above helpful suggestions for COUNTIF, I can't
get anything like the following to work:

arr = Application.VLookup(10035,A1:E5,????????,False)

Any more helpful suggestions?

Thanks in advance,
Alan Beban