Speeding up a custom lookup function

I've been writing a custom function to sum numbers from a matrix based on multiple criteria. One criterion is matched against the values in the first row of the matrix, one against the values in the left most column of the matrix and one matched against any cell in the matrix.
When all criteria - if not optional - are matched the value offset to the last found cell are added to a total.
Finally the resulting total is returned by the function.

Problem with this function is that it is terribly slow.
Can anyone give me suggestions/directions on how to speed it up?

Please Login or Register  to view this content.

Hi, Tsjallie,

depending on how often the values will appear in the matrix you could either use WorksheetFunction.Match for one item or Find/FindNext for more items.

Ciao,
Holger

Hi Holger,
thanks for your suggestion.
As I need one item at time I think the match-function can be a serious option for the horizontal and vertical lookups.
It worries me that I didn't come to think of that myself
I'll have a play with it and let you know the results.

Hi Holger,
I think it's gonna be the find function after all.
Plan is to create arrays with matching columns and matching rows and then using the index function to evaluate the individual cells (floating criteria).
I'm sure this will speed up things, though I haven't found the time yet to finish the function.