Hi Kane,
UDFs are slow, but that's only relative.
I did a few tests on my almost 5 years old Pentium.
I copy my VBA UDF below. It's about a 100 times faster than a workssheet VLOOKUP with FALSE as a 4th argument.
I also include an XLM version (not to be confused with XML); it is again twice as fast.
Both solutions have a simple, understandable User Interface.
A combination of MATCH and INDEX is again 10 times faster, but is a bit more tedious to implement ("a monster", as you chose to
call it).
But what are we talking about?
My example (which I can mail you if you wish), has a 64k table and 1000 VLOOKUP formulas, with random search arguments.
VLOOKUP/FALSE solution: 5 seconds.
VBA solution: 0.11 seconds
XLM solution: .055 seconds
MATCH/INDEX solution: .0043 seconds
Even on my oldtimer it's impossible to notice the difference between the last 3 solutions. Of course, if you would have 64K
formulas, it could be noticed.
There are also differences if the number of matches varies. One would think that more matches mean more double VLOOKUPs so require
more time, but that does not seem to be the case. Apparently returning #NA requires more time.
The XLM solution could be almost twice as fast again if I could find a way to imitate the MATCH/INDEX approach. But unless I
require the lookup table to be passed as an argument twice (once only the fisrt column, once the whole table) I don't know how to
isolate the first column for the MATCH function. My XLM knowledge has faded away quite a bit!
Any suggestions are welcome!
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
=================================================================
Function VLookupSort(SearchArgument As Range, SearchTable As Range, _
ColumnNo As Long, Optional SortDirection, Optional NotFound)
' Works as Vlookup, exact match (4th argument = FALSE)
' But takes advantage of the fact that a table is sorted
' and thus is much faster
' Also permits table to be sorted descending (Sortdirection -1)
' Optional argument for return value if item not found, defaults to #NA
Dim ItemFound
If IsMissing(SortDirection) Then SortDirection = 1
ItemFound = Application.Match(SearchArgument, Intersect(SearchTable, SearchTable.Cells(1).EntireColumn), _
SortDirection)
If SearchTable(ItemFound, 1) <> SearchArgument Then
If IsMissing(NotFound) Then
VLookupSort = CVErr(xlErrNA)
Else
VLookupSort = NotFound
End If
Else
VLookupSort = _
SearchTable(ItemFound, ColumnNo)
End If
End Function
=================================================================
The XLM solution. Create an XLM sheet; CTRL+F11. Paste the code below into A1 and down.
Insert a defined name (VlookupSortXLM) for A1.
=================================================================
VlookupSortXLM
=ARGUMENT("SearchArgument",1)
=ARGUMENT("SearchTable",8)
=ARGUMENT("ColumnNo",1)
=VLOOKUP(SearchArgument,SearchTable,1)
=IF(A5=SearchArgument,VLOOKUP(SearchArgument,SearchTable,ColumnNo),NA())
=RETURN(A6)
=================================================================