Excel VBA - using lookup functions on a two-dimensional array

[Harry Flashman]

I am trying to figure out how to use Excel lookup functions (such as VLOOKUP, HLOOKUP, INDEX, and MATCH) to return the data according to certain criteria from a two-dimensional array.

Let's say I have an two dimensional array like the following:

Sub LookupArray()
Dim MyArray(0 To 1, 0 To 3)

MyArray(0, 0) = 1
MyArray(0, 1) = 2
MyArray(0, 2) = 3
MyArray(0, 3) = 4

MyArray(1, 0) = "A"
MyArray(1, 1) = "B"
MyArray(1, 2) = "C"
MyArray(1, 3) = "D"

End Sub

And let's say I want to return the Letter based on a numeric value in my array.

Dim MyStr As String
MyStr = WorksheetFunction.HLookup(3, MyArray, 2, 0)

The above code returns C.

But what if my criteria value is B, for example. How can I get it to return the corresponding number?

Likewise, let's say I want to return the position of a numeric value the above array I can use the MATCH and INDEX functions.

Dim i As Integer
i = WorksheetFunction.Match(3, WorksheetFunction.Index(MyArray, 1, 0), 0)

The above code returns 3.

But how can I do the same thing with a letter? The various permutations I have tried keep returning error messages.

Any help would be greatly appreciated. Cheers.

 

[Harry Flashman]

This happens to me every time. I spend ages messing around getting nowhere. Then I post to a forum, and then minutes later I figure it out.

This will return the position of the letter within row 2

MyStr = WorksheetFunction.Match("C", WorksheetFunction.Index(MyArray, 2, 0), 0)
Debug.Print MyStr ' returns 3

This will return the corresponding numeric value (from row 1) based on a known letter (from row 2).

MyStr = Application.Index(MyArray, 1, WorksheetFunction.Match("D", WorksheetFunction.Index(MyArray, 2, 0), 0))
Debug.Print MyStr ' returns 4
End Sub

 

[Harry Flashman]

I suppose this bring me to my next question. Is it better to find a value within an array using an Excel function or is it better to loop through the array and find the value that way?