Hi all again!

First I want to say sorry that I didn't respond until now, but I've
been away for a business trip the last days and had no internet. I
also want to apologize right now for my bad english - its not my
native language. But it seems until now you all understood my
problems :-)

I coded a bit at home and used another testing environment (an old pc)
and could improve time differences significantly (65000 lookups with
vlookup / vba-code / double-match-index-thingy: 200s / 24s / <1s - how
did you measure your calculation times, niek?).
but you see, my own solution is still much worse than the ugly code.

@charles: this information about whether VBE is open or F9 is used is
really interesting. I will try a Application.Calculate button.
I did transfer the SearchRange as Range object, but not the argument -
maybe there is also a slowdown there. Need to find time to test it.
The same for Application.WorksheetFunction.***

@niek:
your solution looks quite nice. I hopefully will test it soon.
Since you say that returning #NA needs a lot of time, maybe an error
handler can help here. I think I read some time ago that transfering
errors in VBA is time consuming. Maybe worth a try.
defining the searchargument as range might be a key element to speed
up my own code which extracts the value of the cell right now.
Also your XLM solution I need to try first, but I have to admit that I
never came in contact with XLM before (at least I don't remember such
an occasion). Might be a bit harder for me to do more than copy&paste
here :-)


Another idea of mine is to create a macro which replaces all commands
with name "fastvlookup" (or any other custom name) and some parameters
with the already well known double-match-index-monster. fastvlookup
needs not to be known to excel for this to work, of course. But first
I will try nieks solution :-)



PS: It would be a great thing if excel has some kind of "issorted"
flag for columns/rows, which has positions "true/false/don't know".
every time you ask for this flag it is recalculated and if the column/
row is changed it is resetted to "don't know". But that's only wishful
thinking....




On 26 Jul., 22:50, "Niek Otten" <nicol...@xs4all.nl> wrote:
> 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 workssheetVLOOKUPwith 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 1000VLOOKUPformulas, 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 thefisrt 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 asVlookup, 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,Searc hTable,ColumnNo),NA())
> * * * =RETURN(A6)
> ================================================== ===============
>