Excel-DNA XFunctions Add-in

ExcelDna.XFunctions.xll is a small add-in that implements two user-defined functions - XLOOKUP and XMATCH - that are compatible with the newly announced built-in functions.

For some great material (including videos) on the new functions see the Bill Jelen (Mr. Excel) site - The VLOOKUP Slayer: XLOOKUP Debuts in Excel.

XFunctions is meant to be a completely compatible implementation that covers the full functionality of XLOOKUP and XMATCH.

The add-in should work in all Windows versions of Excel, with separate 32-bit and 64-bit add-ins. If the add-in is loaded into a version of Excel that already supports the XLOOKUP and XMATCH functions, the add-in will register the add-in functions under the names XLOOKUP.FROM.ADDIN and XMATCH.FROM.ADDIN respectively.

Getting Started

Binary releases are hosted on GitHub: https://github.com/Excel-DNA/XFunctions/releases

Here are some example workbooks with data from the online help and other blogs posts showing the new functions: https://github.com/Excel-DNA/XFunctions/tree/master/Examples

Examples

_(Note that the examples below were created before the [if_no_match] parameter was added to XLOOKUP.)_

The HelpExamples workbook contains a number of examples corresponding to the online help documentation for the respective functions.

XLOOKUP Example 1

XLOOKUP Example 3

XMATCH Example 1

Notes

I've not seen the real XLOOKUP or XMATCH functions myself, so haven't been able to compare this implementation.
If you try to debug and see a "Managed Debugger Assistant" message relating to the "LoaderLock" just ignore it... (it is caused by the Excel-DNA IntelliSense extension and should not be a concern)

TODO

Understand compatibility for sheets between real functions and the XFunctions version - internally the workbook knows whether a function in a formula is a built-in function or an xll function . . . how does it behave when loaded backwards or forwards?
Clean up, add tests etc.
Fix up example books again, in a version where XLOOKUP is not yet supported.

Support and participation

Any help or feedback is greatly appreciated.

"We accept pull requests" ;-)

Please log bugs and feature suggestions on the GitHub 'Issues' page.

For general comments or discussion, use the Excel-DNA forum at https://groups.google.com/forum/#!forum/exceldna .

License

This project is published under the standard MIT license.

Govert van Drimmelen

govert@icon.co.za

31 August 2019