Don,

The only apparent difference is the way errors are handled (returning an
error value as opposed to raising an error), so I don't know why
WorksheetFunction is faster, it just is (20-30%).

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Don Guillett" <dguillett1@austin.rr.com> wrote in message
news:%23ggRliO7IHA.1420@TK2MSFTNGP06.phx.gbl...
> Charles, Please share why is this so?
>
> Also worth noting that using Application.WorksheetFunction.Match is faster
> that using Application.Match.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "Charles Williams" <Charles@DecisionModels.com> wrote in message
> news:eCVGJTO7IHA.1592@TK2MSFTNGP04.phx.gbl...
>> You can only make Excel MATCH and VLOOKUP use binary search by telling
>> them that the data is sorted: there is no automatic detection of sorted
>> data.
>> The drawback to using binary search as implemented by Excel is that you
>> get the nearest match for missing data rather than #N/A, so you have to
>> use the trick outlined by Niek (note the 1 as final Match argument) to
>> trap data not found.
>> See also http://www.decisionmodels.com/optspeede.htm
>>
>> There is a long-standing bug associated with VBA UDFs that refreshes the
>> VBE title bar once for each calculated instance of a UDF, unless
>> calculation is initiated from VBA (using something like
>> Application.Calculate).
>> This bug drastically slows down Excel calculations if you have large
>> numbers of UDFs in formulae, unless you bypass by using Manual Calc mode
>> and trapping F9 etc.
>> See also http://www.decisionmodels.com/calcsecretsj.htm
>>
>> Assuming that a UDF LOOKUP/MATCH is correctly programmed to use Range
>> objects etc, thus avoiding the Excel to VBA data transfer overhead, and
>> avoids the VBE refresh bug, it will be very nearly as fast as a native
>> LOOKUP/MATCH, and faster if programmed to avoid the double LOOKUP or
>> using better algorithms.
>>
>> Also worth noting that using Application.WorksheetFunction.Match is
>> faster that using Application.Match.
>>
>>
>> Charles
>> __________________________________________________
>> The Excel Calculation Site
>> http://www.decisionmodels.com
>>
>> "kane" <daum.sebastian@googlemail.com> wrote in message
>> news:82f0ba5d-ce41-4fd5-8962-7b38d84d45f3@k13g2000hse.googlegroups.com...
>>> On 22 Jul., 17:15, "Niek Otten" <nicol...@xs4all.nl> wrote:
>>>> If your data is sorted:
>>>>
>>>> Table in A1:B5, lookup value in C1.
>>>>
>>>> In D1:
>>>> =MATCH(C1,A1:A5,1)
>>>> In E1:
>>>> =IF(INDEX(A1:A5,D1)=C1,INDEX(B1:B5,D1),NA())
>>>>
>>>> Lightning fast and returns #NA if no exact match.
>>>>
>>>> --
>>>> Kind regards,
>>>>
>>>> Niek Otten
>>>> Microsoft MVP - Excel
>>>>
>>>
>>> That's exactly what I meant with "match-index-monster". I would like
>>> to have a simple function that also my colleagues could use.
>>>
>>>
>>> @Pete: I assumed that "match" does a binary search if you use it on a
>>> sorted list (with parameter FALSE), so no need to program this search
>>> by myself. I also read that UDFs are much more slowly, pitily.
>>>
>>> Right now I wonder whether match does really do a binary search or
>>> whether vlookup does sort large lists hidden in memory to speed up
>>> things.
>>> i created 3 excel test sheets. in all three there are 40.000 values to
>>> look up and the vector to be searched is of the same size. hence, a
>>> normal vlookup should normally use about 40.000*(a*40.000+d)
>>> operations. dobule-match-index-lookups should have about
>>> 40.000*(b*2*(lb 40.000)+c+d) operations. a is the time vlookup needs
>>> to check one cell, d is the time to transfer cell value, set flags and
>>> so on, b is the time match needs for one calculation step, c the time
>>> for index to extract a certain cell value. considering different
>>> code... well there should still be at least 99% less time needed if
>>> double-match-index-lookup is used.
>>> i tested vlookup vs. my code vs. double-match-index-lookup and those
>>> are the times:
>>> 52 sec / 13 sec / 12 sec
>>>
>>> the good message in here: my own code works almost as fast as the
>>> match-index-solution which is 4 times as fast as vlookup.
>>> the bad message: either vlookup or match does not work as expected (or
>>> both, of course).
>>>
>>> so... problem solved, but things are still bad :-(
>>
>>
>