Find fastest way to do lookups

F

Fred Smith

I'd like to speed up the execution of my macros. Does anyone have a tip
sheet on this?

In particular, I use a lot of Vlookups. In one case, I need five adjacent
cells on a particular row. Currently, I use 5 Vlookups. Is there a way to
get all 5 at once? And/or should I replace the Vlookups with something else?

Thanks,
Fred
Please reply to newsgroup, not e-mail
 
A

A.W.J. Ales

Fred,

I assume you now do 5 searches in the first column and then "pick up" values
in the second, third , .... column.

To speed this up (not tested, but seems logical) you could first use the
function MATCH() (once) to get the rownumber (within the searchcolumn) of
the item you want, and then use the INDEX() function (5 times) to give you
your values.
Although you then perform 6 functions, the 5 times INDEX() function will run
faster than 4 (additional) VLOOKUPS.
You actually just reduce the number of "searches" at the cost of 1
additional function.

Example :
Data in A10:F14

A B C D E F
--------------------------
10| 100 11 21 31 41 51
11| 200 12 22 32 42 52
12| 300 13 23 33 43 53
13| 400 14 24 34 44 54
14| 500 15 25 35 45 55

MATCH(300,A10:A14,0) gives a result of 3, MATCH(200,A10:A14,0) gives a
result of 2.
To get now the values of the third row (after the value 300; i.e. 13, 23,
33, 43 and 53) you use
INDEX(A10:F14,3,2) ; INDEX(A10:F14,3,3) ; INDEX(A10:F14,3,4) ;
INDEX(A10:F14,3,5) ; INDEX(A10:F14,3,6)
The first 3 (after the range is off course the result of the MATCH().

You could combine both in INDEX(A10:F14,MATCH(300,A10:A14,0),2) ; .... ;
INDEX(A10:F14,MATCH(300,A10:A14,0),6), but that would not be usefull in this
case, because you then again are actually "searching" 5 times again.


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
A

Alan Beban

You might want to consider something like

=VLOOKUP(lookup_value,lookup_table,{2,3,4,5,6},FALSE) arrray entered
into a 5-column row of cells.

Alan Beban
 
A

A.W.J. Ales

Alan,

I do agree with you that arrayfunctions are great to perform some otherwise
impossible actions.
On the other hand however I regularly read that they do slow down
calculations, which is somewhat contradictionary to what the OP asks. Am
I wrong im my assumption to this item ?

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
A

Alan Beban

I guess I'd just test it against whatever you're using now to find out
whether it's useful in your application.

Alan Beban
 
D

Dave Peterson

Do you put the =vlookup()'s in the individual cells or do you do it via code?

If you put the formulas in the cells, I'd use something like Auk's idea. But
I'd insert a helper column that returned the index on the key column.

Then use that in the 5 =vlookup()'s--but now they'd be index()'s.

In F1 (say)
=match(a1,sheet2!a:a,0)
to return the index.

in G1:
=if(iserror(f1),"",index(sheet2!b:b,f1))

in code, you could return the array of values something like this:

Option Explicit
Sub testme01()

Dim res As Variant
Dim myLookUpRng As Range
Dim myCell As Range

Set myLookUpRng = Worksheets("sheet2").Range("a:a")

With Worksheets("sheet1")
For Each myCell In .Range("a1:a" _
& .Cells(.Rows.Count, "A").End(xlUp).Row).Cells
res = Application.Match(myCell.Value, myLookUpRng, 0)
If IsError(res) Then
'do nothing???
Else
myCell.Offset(0, 5).Resize(1, 5).Value _
= myLookUpRng(res).Offset(0, 1).Resize(1, 5).Value
End If
Next myCell
End With

End Sub
 
H

Harlan Grove

Alan Beban said:
I guess I'd just test it against whatever you're using now to find out
whether it's useful in your application.
Click to expand...
....

If speed is the goal, then it all depends on how array arguments are
handled. Some functions can process array arguments themselves, others
require the formula parser to handle array arguments for them. In the latter
case, the formula processor repeatedly evaluates the formula, which is a
performance drag.

VLOOKUP may be one of the functions that handles array arguments, at least
as the 3rd argument, internally. If so, it's more efficient to use one
VLOOKUP call with an array 3rd argument than separate VLOOKUP calls with
scalar 3rd arguments. If not, then the most efficient approach would be

=OFFSET(Tbl,MATCH(v,INDEX(Tbl,0,1),0),1,1,5)

3 explicit function calls should be faster than 5 implicit function calls.
 
A

A.W.J. Ales

Harlan, Alan,

Both thanks for the further explanations.
I assume that the OP now has sufficient material to do extensive testing to
optimize his VLOOKUPS.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Top