I think if you use a FALSE parameter in MATCH or VLOOKUP (i.e. looking
for an exact match), then Excel does a sequential search as the data
does not need to be sorted and there is no way to tell Excel that the
data is sorted (I've often thought that it would be useful to have a
5th parameter in VLOOKUP to inform Excel that the data is sorted).
When you use a TRUE value then the data MUST be sorted beforehand, and
Excel uses a binary search algorithm, which is much quicker.

You can speed things up if you have a lot of VLOOKUPs getting data
from the same matching row by having a MATCH formula in a helper
column to find the matching row, and then have an INDEX formula which
uses the value from the match, so that the searching for the matching
row is only done once.

Hope this helps.

Pete

On Jul 23, 2:52*pm, kane <daum.sebast...@googlemail.com> wrote:

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