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