<div>
i can't conclude that UDF will definitely run faster or slower than Index/Match, a proper test need to be done but i think there are other dependencies. for example, even you create a UDF and use it into your worksheet, it doesn't mean it will run faster. one of the important concerns is that total number of formulas that were used in your worksheets.<br />
<br />
to make your worksheets faster, you can try to reduce the number of formulas using in your worksheets. How can we reduce it? &gt;&gt;&nbsp;we can convert so of them to fixed values when it's feasible.
</div>


<div>
Doesn't matter if you use UDF for Index/Match, processing will be slow as it should be to calculate over 60 sheets.<br />
<br />
Please post your sample workbook, we can create a VBA instead of running 100 thousands INDEX/MATCH formulas.
</div>


<div>
There could be ways to make these workbooks more efficient without resorting to VBA.<br />
<br />
Can you post some examples of these formulas?
</div>


<div>
Maybe your boss needs to read this <a href="https://www.linkedin.com/pulse/20140617001026-4665957-when-a-spreadsheet-makes-your-head-hurt/?trackingId=6M7%2FzysmfU9nn33Qe05C%2BQ%3D%3D" rel="ugc nofollow">highly relevant post on Linked In</a>
</div>


<div>
Hey guys. I totally agree. Cleaning the file and creating VBA to do the calculations instead of formulas would be ideal. One macro can more efficiently yield results than a bunch of formulas. <br />
<br />
Responses:<br />
*Norie: A sample of Index/Match is: Index($A2:B$18,MATCH($A2,A<wbr />$2:A$18,0)<wbr />,2)<br />
*Shums: I would normally love to post his file, but I can't due to confidentiality.<br />
*Ryan: Totally agree. Nonetheless, every cell in all of those sheets update when any other given cell changes. When he changes one value, everything else needs to update and he looks at each cell afterwards to make a decision. Also, about 50 people that know nothing about macro's and Excel make updates to this file and they will add new items/rows/columns anywhere at random that will be in a non-standard format. &nbsp;Therefore, he wants me to increase efficiency in a way where he can always change the layout/formatting without needing support. It's racking my brains a little.<br />
<br />
I guess my initial philosophy was, I could immediately meet his criteria, &quot;Make it run faster without needing support&quot;, by creating a more efficient UDF. It would also be a great learning experience to see if it could be done.
</div>


<div>
Love the article Dale and I totally agree.
</div>


<div>
<blockquote>
 One macro can more efficiently yield results than a bunch of formulas. 
</blockquote>
<br />
Are you sure about that?
</div>


UDF_Index_Match.xlsm

UDF_Index_Match.JPG

After.jpg

Before.jpg

<div>
Thank you! Very interesting ideas!
</div>


<div>
<div class="content wysiwyg-content">
I inherited a bunch of workbooks that each contain over 60 sheets, each &nbsp;containing over 100 thousand Index/Match formulas. It runs pretty slow... <br />
<br />
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? <br />
<br />
It would be pretty cool if we could create UDF's that are more efficient than what Excel provides.
</div>
</div>


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.

Excel 2007: Making Excel better with UDF's

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.