One other thing, the Vlookup could still return #N/A if the data to look up
comes before the first item in the table. Say you are looking up "A" in the
following:

B 1
C 2
F 3
H 4
X 5


If so, you could consider (where D2 is the item to look up in A1:B50000):
=IF(ISNUMBER(MATCH(D2,Sheet1!A$1:A$50000)),IF(VLOO KUP(D2,Sheet1!$A$1:$B$50000,1)=D2,VLOOKUP(D2,Sheet 1!$A$1:$B$50000,2),"Not Found"),"Not Found")

When I tested it on the data set that has 8000 matches (meaning all 8000
formulae had to process the MATCH and 2 VLookup functions) yielded a response
time of 7.5/100 second - still pretty unnoticable.




"JMB" wrote:

"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