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 :-(
>
>