modifiying a custom lookup function
A former collegue created a custom function which would work like vlookup but
find the specified instance of the data being searched instead of being
limited to just the first instance.
Example: VWLookup(PolicyDataSource,"Anderson",5,3)
This would find the 3rd instance of Anderson in the named range
"PolicyDataSource" and pull in the data from the 5th column.
Is there a way to customize this function to allow for an additional
criteria to search on? For example, if column 3 is policy_status and I want
to filter for 'Active'.
Any help is appreciated!
Public Function VWLookup(Table_Array As Object, _
Lookup_Value As Variant, Col_Index_Num As Integer, _
Match_Number As Integer) As Variant
Dim i, j As Integer
On Error GoTo ErrorCatch
For i = 1 To Match_Number
j = Application.Match(Lookup_Value,
Table_Array.Resize(Table_Array.Rows.Count, 1), 0)
If i = Match_Number Then
VWLookup = Application.VLookup(Lookup_Value, Table_Array,
Col_Index_Num, 0)
Exit Function
End If
Set Table_Array = Table_Array.Offset(j,
0).Resize(Table_Array.Rows.Count - j)
Next i
ErrorCatch:
VWLookup = "N/A"
End Function
|