Excel Xlookup [How To Do Excel Xlookup]

Excel Xlookup - How To Video

Xlookup is a replacement function for the widely used and extremely popular vlookup and hlookup functions. With this new function, Microsoft has been able to add some new features to the vlookup function that make it easier and more straightforward to use as it is more flexible. The XLOOKUP function is built into the latest version of Excel, for previous versions there exists the free Excel add-in Xfunction.

In this video, we explain each parameter that can be used in the xlookup function. There are three required parameters and three optional ones. The parameters that should be inserted are the lookup value, the lookup range, and the return range. When we only insert these values, we use the standard version of the xlookup function which is the first one we explain in the video. Next to showing how to use this, we also explain the flexibility of this standard version together with the differences from vlookup. Two of these flexibilities, i.e. backward-looking and returning multiple lines are explained and shown in detail.

After deep-diving into the standard version of xlookup, we move on to the optional parameters to explain. There are three optional parameters: the if not found value; the match mode and the search mode. The if not found parameter can be used to show a certain message when the lookup value is not found in the lookup range. In the standard setting, xlookup will use an exact match as match mode. This means that an output value is only returned when an exact match of the lookup value is found in the lookup range. Other potential match modes are an exact match or the next smaller value, an exact match or the next larger value and the wildcard mode which allows you to search for a certain characteristic of a string, this way you can search for a string containing the letters a s for example.

The last optional parameter is the search mode, this can be used to indicate that you want to search in ascending or descending order. You can also indicate to use a binary search, this is a search algorithm implemented by Microsoft which aims at being time-efficient and is preferable when working with massive data sets, but should be handled with care as it can only be used on sorted lookup ranges. Each of these optional parameters together with their potential values are explained in the video using different examples that conclude the video. ... https://www.youtube.com/watch?v=xR-Iu5IVtno