XFuntion is free Excel addin to perform newly introduced built-in functions in Excel; XLOOKUP and XMATCH. Here this plugin lets you use these two functions as user defined function. Although these functions will ship with later Excel versions but it is not guaranteed that these functional will be added to the older version of Excel. Also, these functions are not yet available publicly as they are in the insider program of Office 365. But if you still want to use these functions in whatever version of Excel you are using then you have come to the right place.
XLOOKUP is the successor of the legendary VLOOKUP function of Excel. VLOOKUP is always been of the most used Excel function of all time. It was introduced in the Excel 1 and can still be seen. But last week, Microsoft has added new built-in function in insider program of Office 365. The major difference here is that like the traditional VLOOKUP and HLOOKUP, here you don’t have to specify row or column index. So, in case you delete or add new column in your sheet then you won’t have to modify the XLOOKUP formula.
The new functions can be used on any version of Excel and work in the same way as shown in the introductory videos. You just add this plugin and then simply start using on any spreadsheet. The XLOOKUP formula is similar to VLOOKUP and HLOOKUP functions. All you need to do is just install it on 32 or 64 bit version of Excel that you are using and then you are simply done.
How to use new XLOOKUP Excel Function in All Versions of Excel?
You can simply download the XLL file of this addin from here based on the version of Excel you are using. Next thing is to install the addin in Excel. To do that, go to Addins in the Options section. Next just use the Browser button to locate the XLL file that you have downloaded and then you done. You can now start using the XLOOKUP function in the spreadsheet.
Open a spread sheet and then use the newly added XLOOKUP function as follows. The syntax of the addin is similar to what you see in the traditional VLOOKUP. The difference here is that you don’t have to specify the id of the column. And you can use the same function for HLOOKUP as well. Also, here the exact match option is enabled by default.
XLOOKUP(lookup_value, lookup_array, return_array)
lookup_value: The cell value that you want to lookup in the table.
lookup_array: The column in the table to specify where to find the input value.
return_array: Here you specify the column directly from which you want it to return the final result.
This is all about this simple and powerful plugin for Excel to use the XLOOKUP. When you use it for a few times, you will easily able to use it in daily use. Apart from the aforementioned three parameters, it supports two more optional parameters. The fourth parameter is to specify match mode(exact or approximate match). And the fifth parameter is for specifying the search mode to specify type and direction of search. You can read more about this function on official blog of Microsoft that I have already linked in the beginning paragraphs.
Closing thoughts
This is all about this simple and powerful Excel plugin, XFunction to add support for XLOOKUP in all versions of Excel. This plugin worked fine during my testing, however, it should be noted this is a just released a few days ago and it may contain some bugs. So if you encounter any bugs, do not forget to report them on its GitHub page. I tried this plugin on 64 Bit Excel 2016 and it worked like charm. But it should work perfectly on Excel 2013 and 2010 without any problems and if it doesn’t then you can tell me in the comments down below.
