Harry Flashman
Active Member
- Joined
- May 1, 2011
- Messages
- 361
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:
And let's say I want to return the Letter based on a numeric value in my array.
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.
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.
Let's say I have an two dimensional array like the following:
Code:
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.
Code:
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.
Code:
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.
Last edited: