"Which will make it extremely slow" or which did make it extremely slow? Did
you try it or are you assuming? I tested it on two data sets, matching a
table of 8000 to a table of 50000, one with only 27 matches and the other
with 8000 matches.

The calculation time was 0.0498721 and 0.05175028 seconds, respectively.

If you want faster than 5/100 second response time, there's nothing much
else I can suggest.

For anyone reading, that tip, I'm pretty sure, came from the book,
Professional Excel Development, by Rob Bovey, Stephen Bullen, and John Green.



"Henk57" wrote:


Thanks, but I am afraid not quite what I mean. This formula contains
TWO VLOOKUP functions which will make it extremely slow.... Remember,
I have to lookup 8000 times in a 50000 dB. The approx lookup may help;
I always thought this affects numbers only.

JMB Wrote:
since your data is sorted, why not use an approximate match Vlookup, it
is
much faster. If the table is in A1:B3 and the value to look up is in
D1,
then try:

=IF(VLOOKUP(D1,A1:B3,1,TRUE)=D1,VLOOKUP(D1,A1:B3,2 ,TRUE),"Not Found")

change ranges as needed.

"Henk57" wrote:
-

Hello:
I want to compare a dbase of 50000 or so with a new one of 8000
records. The purpose is to add the new ones only, so every record in
the newly establisehd dB remains clean with unique records. With a
simple VLOOKUP (using a fixed array with absoulte first and last cell
addresses) I get 50,000 x 8,000 calculations and I want to do this
much
more intelligently. My idea is to sort both data bases
alphabetically,
and to make the array defined in the VLOOKUP formula dynamically,
i.e.
for instance only using the part that starts with the same first
letter
of the record. Is this a sensible/possible approach? And if so, has
anyone an idea how to do that?




--
Henk57
-




--
Henk57