MATCH, INDEX and VLOOKUP with Arrays

[Guest]

Ok now I'm confused... With Support from this forum, I was able to use
VLOOKUP with 2-Dimensional arrays using Variants and ReDims. I thought this
would also work with INDEX and MATCH functions of Excel. However, I keep
getting the error 2042 which I believe means that the values was not found
(equivalent for false in VLOOKUP). However, I was able to find the value in
the array manually and the array is sorted by the first column. The search
value is in a variant variable and the 2-Dimensional array is a variant redim
to (1 to 500 , 2).

Does the INDEX, MATCH function not work with 2-Dimensional arrays? Please
any help is appreciated.

Dean.

 

[Tushar Mehta]

Even when used in a worksheet, MATCH doesn't work with a anthing other
than a single column (or row).

When using XL functions in VBA, you need to first understand how they
work in XL and then how that functionality maps into VBA. Since MATCH
only works with a single column (or row) in XL, it should not be a
surprise that it doesn't work with a 2D array in VBA. The lookup
functions, VLOOKUP and HLOOKUP, on the other hand, are designed for use
with a 2D range.

Assuming A1:A3 contain the numbers 1,2,3 and B1:B3 contain 4,5,6, the
1st 2 lookup statements work OK returning 5 and 2 respectively. The
last one doesn't.

Sub testIt()
Dim v, v1
v = Range("A1:B3").Value
v1 = Range("a1:a3").Value
MsgBox Application.WorksheetFunction.VLookup(2, v, 2)
MsgBox Application.WorksheetFunction.Match(2, v1, 0)
MsgBox Application.WorksheetFunction.Match(2, v, 0)
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tom Ogilvy]

Index works with a two dimensional array, but there is search involved with
index. If you are using match to return an offset into index, then the
match is the limitation.

the quick answer on Match is it only works with a 1D array - however it does
work with a two D array, but the 2 D array would have to be single column or
singe row.

You can use index to extract a single column or single row from the array to
use with match, so it would be (pseudo code)

index(array,match(value,index(array,0,1)),1)

also, in earlier version of Excel, working with an array imposes a 5461
element limit I believe.

Given a database starting in B5 with header is row 5 and order number in
Column B.
(2055 rows and 10 columns)

Here is a demo with everything broken into pieces:

Sub Demo2()
Dim v As Variant, res As Variant
Dim v1 As Variant, res1 As Variant
Dim v2 As Variant, res2 As Variant
Dim rng As Range
' restict size to accomodate earlier versions
Set rng = Range("B5").Resize(1000, 5)
v = rng.Value
v1 = Application.Index(v, 0, 1)
v2 = Application.Index(v, 1, 0)
res1 = Application.Match(10254, v1, 0)
res2 = Application.Match("Quantity", v2, 0)
If Not IsError(res1) And Not IsError(res2) Then
res = Application.Index(v, res1, res2)
If Not IsError(res) Then
MsgBox "Order Number 10254 has a quantity of " & res
Else
MsgBox "Order Number 10254 was not found"
End If
Else
MsgBox "either 10254 or Quantity was not found"
End If


End Sub

Note, in earlier versions of Excel, changing 5 to 6 would cause an error in
the routine (type mismatch, indicating more than 5461 elements)

this puts it all together

Sub Demo1()
Dim v As Variant, res As Variant
Dim rng As Range
Set rng = Range("B5").Resize(100, 5)
v = rng.Value
res = Application.Index(v, Application.Match(10254, _
Application.Index(v, 0, 1), 0), Application.Match("Quantity", _
Application.Index(v, 1, 0), 0))
If Not IsError(res) Then
MsgBox "for Order: 10254, Quantity is " & res
Else
MsgBox "Order 10254 was not found"
End If
End Sub