Igor Spryzhkov
asked on
How to improve custom made function VLOOKUP2
Dear members of Expert Exchange!
I want to introduce custom made function VLOOKUP2. The source is http://www.planetaexcel.ru/techniques/2/100/
This function is powerful. You may:
- search in any column of range (in data base);
- define index number in search result.
Look at attached XLS-file for examples.
But there is a room for improvement (original function have slow algorithm)
Tasks for optimization:
- change from loop to .find (but I not know .find syntax);
- optimize search algorithm if user want to retrieve only first value in records set;
- optimize search algorithm if user want to retrieve not first value in records set;
- optimize search algorithm if user want to retrieve more as one value in records set;
- add option for return count of records set.
Thanks in advance for help!
P.S. Please sorry for my English!
VLOOKUP2-eng.xls
I want to introduce custom made function VLOOKUP2. The source is http://www.planetaexcel.ru/techniques/2/100/
This function is powerful. You may:
- search in any column of range (in data base);
- define index number in search result.
Look at attached XLS-file for examples.
But there is a room for improvement (original function have slow algorithm)
Function VLOOKUP2(Table As Variant, SearchColumnNum As Long, SearchValue As Variant, _
N As Long, ResultColumnNum As Long)
Dim i As Long, iCount As Long
Select Case TypeName(Table)
Case "Range"
For i = 1 To Table.Rows.Count
If Table.Cells(i, SearchColumnNum) = SearchValue Then
iCount = iCount + 1
End If
If iCount = N Then
VLOOKUP2 = Table.Cells(i, ResultColumnNum)
Exit For
End If
Next i
Case "Variant()"
For i = 1 To UBound(Table)
If Table(i, 1) = SearchValue Then iCount = iCount + 1
If iCount = N Then
VLOOKUP2 = Table(i, ResultColumnNum)
Exit For
End If
Next i
End Select
End Function
Tasks for optimization:
- change from loop to .find (but I not know .find syntax);
- optimize search algorithm if user want to retrieve only first value in records set;
- optimize search algorithm if user want to retrieve not first value in records set;
- optimize search algorithm if user want to retrieve more as one value in records set;
- add option for return count of records set.
Thanks in advance for help!
P.S. Please sorry for my English!
VLOOKUP2-eng.xls
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Steve,
I can only assume it's because Match is a pure Excel function, whereas Find isn't. (It may also help that Match has less functionality than Find().)
I suspect that from now on, my decision rules will be...
- Use the Cell method for non-expert OP's or when the table is small enough for acceptable response times.
- Use the Array method if the Cell method is too slow.
- Use the Match method if the Array method is too slow.
Regards,
Brian.
I can only assume it's because Match is a pure Excel function, whereas Find isn't. (It may also help that Match has less functionality than Find().)
I suspect that from now on, my decision rules will be...
- Use the Cell method for non-expert OP's or when the table is small enough for acceptable response times.
- Use the Array method if the Cell method is too slow.
- Use the Match method if the Array method is too slow.
Regards,
Brian.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The_Barman, Brian, Patrick! THANKS!
These are ultimate answers and amazing set of solutions!
These are ultimate answers and amazing set of solutions!
ASKER
Another issue for UDF "VLOOKUP_Plus" is add approximate search option
New related question: "Approximate search in text data (Excel VBA, Excel SQL)"
https://www.experts-exchange.com/questions/28010310/Approximate-search-in-text-data-Excel-VBA-Excel-SQL.html
New related question: "Approximate search in text data (Excel VBA, Excel SQL)"
https://www.experts-exchange.com/questions/28010310/Approximate-search-in-text-data-Excel-VBA-Excel-SQL.html
Thanks, Last_Free_Man, especially for the question!
I hadn't considered the MATCH function as I would have expected it to be in line with the FIND method and ultimatelty slower than the Array method. This is something I will certainly consider in future.
As with a lot of things in Excel, this will come down to how the function is to be used in the real application. But for me one of the things I always appreciate about this site is that you never cease to learn something new on a pretty regular basis.
Thanks,
Steve.