Microsoft recently announced some changes to its products that it releases on a monthly basis. These updates vary from being minor bug fixes all the way to new features. This month had a handful of each but buried in some of the changes was the addition of a new function for Excel.
You may or may not already be familiar with HLOOKUP & VLOOKUP, both of which are incredibly useful when it comes to looking for and returning sets of data. Well now (*drum roll*) there’s XLOOKUP! This modern replacement will see HLOOKUP & VLOOKUP be overtaken by the powerful XLOOKUP. If you used either HLOOKUP or VLOOKUP you’ll know that although they are useful, on occasion they can be tricky to wrangle into given you the result you expect. Now, with XLOOKUP, the process has been streamlined to provide you with three straightforward criteria rather than a shot in the dark. The three criteria are: lookup_value (what you are looking for); lookup_array (where to find it); return_array (what to return).
There are some gripes with VLOOKUP that XLOOKUP solves. For starters you cannot search for items toward the left of your data, only to the right. Whilst most formulas and functions will update when you add columns or rows in, VLOOKUP will not do this, potentially causing issues.
It may not mean much to those not well versed in Excel, but regardless, this is a change that cannot be understated. It will now make searching for and returning data in your spreadsheets a whole lot easier and removes all the potential roadblocks and issues that other solutions (INDEX/MATCH) provide. There are some optional criteria that allow you to make some tweaks to the returned values, that dissimilar to H&VLOOKUP allow you to refine and return more exact results.