XLOOKUP Great News for Excel Power Users
The launch of the new XLOOKUP function for Microsoft Excel has created quite a stir in the Excel world – we at ClusterSeven are equally as excited. With Excel being the de facto data manipulation tool in business, using poor quality formulae or non-standard workarounds has been a constant source of problems for users, impacting business processes supported by their spreadsheets.
So, what’s the fuss about? XLOOKUP addresses some shortcomings of VLOOKUP, which has been one of Excel’s key features since 1985. VLOOKUP, is used for selecting information in vertical columns – just as HLOOKUP is used for selecting data from horizontal columns. Both these functions had limitations that caused inadvertent errors and mistakes for users, especially as many often use spreadsheets in the same way as they use databases. For instance, VLOOKUP can only select data to the right of a reference column, so users have to use workarounds and make adjustments to their spreadsheets to use this function. These workarounds, despite the best will, can be error prone, and cause huge impact on the business, especially as spreadsheets lack the robust functionality of databases, such as change control or rollback, that mitigate the risk of inaccuracies, faults and mistakes.
XLOOKUP (‘X’ for exact) will now allow users to search for a term in one column and return from the same row in another column – regardless of which side the reference column is on. This functionality is easier to use, more reliable and robust too. It also addresses issues like ensuring there is an exact match for a search, where previously approximate results often featured.
Still in beta, Microsoft is aiming to roll the functionality out more widely in due course. So, while VLOOKUP and HLOOKUP functionality will still be supported by Microsoft in the near term, users should be aware that they will need to upgrade to the latest version of Excel for XLOOKUP. This means that it is most likely that organizations will be running different versions of Excel in parallel – unless of course they make a wholesale upgrade to the latest version.
XLOOKUP will make many people’s life that bit easier and organizations will be looking to implement this as soon as. For Excel Geeks, Christmas has come very early!