Archive for July, 2010

HVLookup UDF

The following function can be used to perform a simultaneous Horizontal and Vertical Lookup on a matrix:

Public Function HVLookup(RowLabelValue As Variant, _
ColHeaderValue As Variant, _
objRange As Object, _
Optional vntShowErrBox As Variant) As Variant
'Pass in the values to be matched in matrix column
'header (ColHeaderValue), the matrix row label (RowLabelValue), the
'entire matrix range (objRange), and, optionally, whether or not you
'want to see an error message pop up if not match is found

Dim vntPosHoriz As Variant
Dim vntResult As Variant
Dim strSheet As String

With Application
'Set to volatile so that this function will auto update
'like a native function.

strSheet = objRange.Parent.Name
'Name of the worksheet on which the function is located (only
'needed for error message).

vntPosHoriz = .Match(ColHeaderValue, objRange.Rows(1), 0)
If IsError(vntPosHoriz) Then
If Not IsMissing(vntShowErrBox) Then
MsgBox CStr(ColHeaderValue) & " Does Not Exist in " _
& "Range [Sheet='" & strSheet & "']", _
32, "HVLookup Function Error"
End If
HVLookup = 0
Exit Function
vntResult = .VLookup(RowLabelValue, objRange, vntPosHoriz, False)
End If
End With

'Return result
If IsError(vntResult) Then
HVLookup = 0
HVLookup = vntResult
End If

End Function


Read Full Post »