I inherited a bunch of workbooks that each contain over 60 sheets, each containing over 100 thousand Index/Match formulas. It runs pretty slow...

The owner wants to increase performance, but loves his formulas that update on the fly. I was thinking, if I could give him a User Defined Function that runs faster than Index/Match, that would get him part of the way there. Is this possible? If so what would be the process/VBA code?

It would be pretty cool if we could create UDF's that are more efficient than what Excel provides.

The owner wants to increase performance, but loves his formulas that update on the fly. I was thinking, if I could give him a User Defined Function that runs faster than Index/Match, that would get him part of the way there. Is this possible? If so what would be the process/VBA code?

It would be pretty cool if we could create UDF's that are more efficient than what Excel provides.

to make your worksheets faster, you can try to reduce the number of formulas using in your worksheets. How can we reduce it? >> we can convert so of them to fixed values when it's feasible.

Please post your sample workbook, we can create a VBA instead of running 100 thousands INDEX/MATCH formulas.

Can you post some examples of these formulas?

I can remember one spreadsheet I inherited which had a bunch of formulas (not all in the same column), interspersed throughout a worksheet, which were over 500 characters long, and the creator of the spreadsheet gave no explanation, anywhere in the workbook, of what they were supposed to do. This spreadsheet was crazy and we eventually rewrote many of those formulas as UDFs, which we could document so people could understand what they were attempting to do.

I doubt that you are going to provide much speed improvement over the Excel functions, but if you have a bunch of those functions linked together, as described above, you might see some improvement. You will definitely be able to understand better what is going on behind the curtain.

This is just an example, Assuming you have ID's in Col A & Employee Names in Col B you and you need to have Employee Names based on ID:

Named Range for IDNumbers:

```
=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A$2:$A$1001))
```

Named Range for Employee's NameList:```
=OFFSET(Sheet1!$B$2, 0, 0, COUNTA(Sheet1!$B$2:$B$1001))
```

Then UDF for Index Match:```
Function MyMatch(LookupValue As Variant, RngReturn As Range, RngSearch As Range, Optional HorizontalMatch As Boolean) As Variant
If HorizontalMatch = True Then
MyMatch = WorksheetFunction.Index(RngReturn, 0, WorksheetFunction.Match(LookupValue, RngSearch, 0))
Else
MyMatch = WorksheetFunction.Index(RngReturn, WorksheetFunction.Match(LookupValue, RngSearch, 0), 0)
End If
End Function
```

Suppose you have drop drown for IDs in E1 and you need result in E2:```
=MyMatch(E1,NameList,IDNumbers)
```

Check below screen shot:Now converting this one formula into 60 sheets would be difficult.

UDF_Index_Match.xlsm

and unless you looked at the code you might have no idea that the HorizontalMatch property was optional.

If you are interested in making a small adjustment so that it looks like this

where it's clear that HorizontalMatch is optional then take a look at my Creating your own Excel formulas and doing the impossible article.

If several columns use the same match, a helper column making the match once, can increase speed significantly.

If the UDF does the same as the build in function, it is typically slower, but sometimes it is possible to do the match another way.

With a long list, say 20,000 rows, the average search rows are 10,000.

If it is numbers (dates are also numbers) and the list is sorted, the UDF could look a the middle value of the range.

If that is greater than the search value, then look at the first half of the range, else the last half.

Repeating this 10-12 times will reduce the rows to less than 10, which can be searched fast.

So with less than 20 match search the result can be found.

With text match, a table could be made once, with the start and end point for the first, or first 2 letters.

Then the UDF could search that limited range.

That also require that the list is sorted.

Using that principle, with Indirect formulas for the range, could do something similar, also with numbers.

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial