I've created a UDF some time ago and used it pretty often till now in different ways (but mainly to compare a "history" of data like having different filters at the same time). While there is no need to change anything at this state, I'd like get some suggestions of how to improve it without the loss of any functionality.
To make it short, it is a LOOKUP-function which goes for the xth match and returns a ref.
Normally there are comments, but I deleted them to test if it is still easy to get what it does. Also i like to have it in a way i don't need them.
Public Function VLOOKUPK(lookup_value As String, lookup_vector As Range, Optional result_vector As Range, _
Optional count_num As Long = 1, Optional case_sens As Boolean) As Variant
Application.Volatile
Dim i(0 To 1) As Long, j As Long, lookup_vector_values As Variant
VLOOKUPK = CVErr(2023)
If count_num - 1 <> Abs(Round(count_num - 1)) Or (i(0) * i(1)) = 1 Then Exit Function
Set lookup_vector = Intersect(lookup_vector.Parent.UsedRange, lookup_vector)
If lookup_vector.Areas.Count > 1 Then Set lookup_vector = lookup_vector.Areas(1)
i(0) = lookup_vector.Rows.Count
i(1) = lookup_vector.Columns.Count
If i(0) > 2 And i(1) > 2 Then Exit Function
If result_vector Is Nothing Then
If i(0) = 1 Or i(1) = 1 Then
Set result_vector = lookup_vector
ElseIf i(0) > 2 And i(1) = 2 Then
Set result_vector = lookup_vector.Columns(2)
Set lookup_vector = lookup_vector.Columns(1)
ElseIf i(0) = 2 And i(1) > 2 Then
Set result_vector = lookup_vector.Rows(2)
Set lookup_vector = lookup_vector.Rows(1)
Else
Exit Function
End If
Else
If result_vector.Columns.Count > 1 And result_vector.Rows.Count > 1 Then Exit Function
Set result_vector = Intersect(result_vector.Parent.UsedRange, result_vector)
End If
If lookup_vector.Columns.Count > 1 And lookup_vector.Rows.Count > 1 Then Exit Function
If Not case_sens Then lookup_value = LCase(lookup_value)
If lookup_vector.Columns.Count > 1 Then j = 1
lookup_vector_values = lookup_vector.Value
i(0) = 1: i(1) = 1
For i(j) = 1 To UBound(lookup_vector_values, j + 1)
If lookup_value Like IIf(case_sens, lookup_vector_values(i(0), i(1)), LCase(lookup_vector_values(i(0), i(1)))) Then
If count_num = 1 Then
Set VLOOKUPK = result_vector.Cells(i(0), i(1))
Exit Function
Else
count_num = count_num - 1
End If
End If
Next
End Function
How to use it:
VLOOKUPK(lookup_value,lookup_vector,[result_vector],[count_num],[case_sens])
lookup_value (required)
- The value to search for.
- lookup_value can be used with wildcards like *.
lookup_vector (required)
- The Range to look in.
- It needs to contain only one row or column if the result_vector is submitted.
- If no result_vector is set and the lookup_vector contains only 1 row/column, the result_vector will be set to the lookup_vector.
- If no result_vector is set, and the lookup_vector contains 2 columns while holding more then 2 rows, the first column will be set as lookup_vector while the second column will become the result_vector. (and vice versa)
- Having a range of 2x2 will result in an error.
result_vector (optional)
- The range where the value to output is located.
- Needs to contain only one row or column.
count_num (optional)
- Indicates the kth match to work with.
- if not submitted, the first occurrence will be submitted.
case_sens (optional)
- if set to TRUE the search will be case sensitive.