code from suggestions in comments here. I will still write it like the first time, so you do not need to read the old question.
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 kth match and returns a ref.
Public Function LOOKUPK(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 'if you encounter errors remove the first >'< LOOKUPK = CVErr(2023) If count_num - 1 <> Abs(Round(count_num - 1)) Then Exit Function ' no natural Number or 0 => exit function If lookup_vector.Areas.Count > 1 Then Set lookup_vector = lookup_vector.Areas(1) 'only first area to work with Set lookup_vector = Intersect(lookup_vector.Parent.UsedRange, lookup_vector) 'skip ranges for speed If result_vector Is Nothing Then 'no output => make one If lookup_vector.Rows.Count = 1 Xor lookup_vector.Columns.Count = 1 Then 'only 1 row/columne + no output = inputrng = outputrng Set result_vector = lookup_vector ElseIf lookup_vector.Rows.Count > 2 And lookup_vector.Columns.Count = 2 Then '2 columns + >2 rows => split for vlookupk-mode Set result_vector = lookup_vector.Columns(2).Cells Set lookup_vector = lookup_vector.Columns(1).Cells ElseIf lookup_vector.Rows.Count = 2 And lookup_vector.Columns.Count > 2 Then '>2 columns + 2 rows => split for hlookupk-mode Set result_vector = lookup_vector.Rows(2).Cells Set lookup_vector = lookup_vector.Rows(1).Cells Else Exit Function 'not supported range-size => exit function End If Else 'got output => check for everything If result_vector.Areas.Count > 1 Then Set result_vector = result_vector.Areas(1) 'only first area to work with Set result_vector = Intersect(result_vector.Parent.UsedRange, result_vector) 'skip ranges for speed If Not (result_vector.Columns.Count = 1 Xor result_vector.Rows.Count = 1) Then Exit Function 'not supported range-size => exit function End If If Not (lookup_vector.Columns.Count = 1 Xor lookup_vector.Rows.Count = 1) Then Exit Function 'not supported range-size => exit function If Not case_sens Then lookup_value = LCase(lookup_value) 'case doesn't matter => make it *lower* Dim cell_count As Long 'for count in For Each Dim cell_value As Variant 'the value in For Each For Each cell_value In lookup_vector.Value cell_count = cell_count + 1 If case_sens Then 'case does matter - check directly If cell_value Like lookup_value Then count_num = count_num - 1 Else 'case doesn#t matter - check lower only If LCase(cell_value) Like lookup_value Then count_num = count_num - 1 End If If count_num = 0 Then Exit For 'item found - skip future loops Next If count_num = 0 Then 'only return something if desired match was found If result_vector.Columns.Count = 1 Then 'only 1 column => select row Set LOOKUPK = result_vector(cell_count, 1) Else 'only 1 row => select column Set LOOKUPK = result_vector(1, cell_count) End If End If End Function
How to use it:
- 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.
- Does not need to be the same type (row/column) like the lookup_vector
- 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.
Edit There was a big fail in the code I didn't noticed till now: It compared with
= instead of
Like. Really sorry. :(