I need a clever VLOOKUP formula

J

JMB

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.
 
H

Henk57

Hello:
I want to compare a dbase of 50000 or so with a new one of 800
records. The purpose is to add the new ones only, so every record i
the newly establisehd dB remains clean with unique records. With
simple VLOOKUP (using a fixed array with absoulte first and last cel
addresses) I get 50,000 x 8,000 calculations and I want to do this muc
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 lette
of the record. Is this a sensible/possible approach? And if so, ha
anyone an idea how to do that
 
H

Henk57

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.
 
J

JMB

"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.
 
J

JMB

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(VLOOKUP(D2,Sheet1!$A$1:$B$50000,1)=D2,VLOOKUP(D2,Sheet1!$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 said:
"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.
Click to expand...
 
H

Henk57

Thank you, JMB. Pretty fast indeed! My initial speed was in the range
of 30 minutes, and I was warned that the 2x VLOOKUP was responsible for
this slow processing ... I shd hv tested yr solution and not have made
an ASS of U and ME indeed.
Henk
 
J

JMB

LOL - that's okay, it's not that long ago I would have thought the same
thing, but I learned (and continue to learn) from people who are much smarter
than I.
 
Top