Help: Two-Dimensional Lookup / Speed

[mikemalachy]

I have created a UDF to make it easier to perform a two dimensional
lookup.

The function is:
Function XYLookup(RowLookupVal, ColLookupVal, table_array as range)
XYLookup = INDEX(table_array, _
MATCH(RowLookupVal,table_array.Rows(1), 0), _
MATCH(ColLookupVal,table_array.Columns(1)), 0)
End Function

I have called this UDF from several hundred cells. I have about 10
small ranges of 15x15 cells in size. They are passed in as the
table_array parameter.

I have two issues that I hope someone can help me with.

1) How can speed up this function? I must use the exact match option.
2) The Recalc time is inconsistent with my expectations on two
different machines. Hitting F9 on a Pentium Xeon Dual Processor
2.4GHz w/2.0GB of Memory takes 25-30 seconds to recalc. Hitting F9 on
a Pentium M 1.6GHz w/0.5GB of memory takes only 2-3 seconds. Both
machines are running W2K and Excel 2000 w/the same Service Pack. The
Laptop has Visual Studio installed. Why would the laptop run so much
faster?

Any help would be appreciated.

Regards,
Mike Malachy

 

[Frank Kabel]

Hi
not a solution but you may have a look at
http://www.decisionmodels.com/optspeed.htm

Though I doubt that your UDF will ever be faster than a direct
worksheet formula as you have added the VBA overhead. for the
differences of your machines:
- maybe settings for a double processor in Windows have something to do
with this

 

[mikemalachy]

Frank,
Great site. Unfortunately, I have checked it out already.

I too wonder if the double processor has anything to do with this. In
the meanwhile, I will try rewriting as the UDF as an XLL.

Thanks for replying.
MM