XLOOKUP: The New Release That Has the Internet Ecstatic over Excel
September 5, 2019
Excel users: rejoice. Microsoft released XLOOKUP in Excel and the internet is so excited, it's meme-worthy. What is it and how will it change your life? Let me explain.
If you use Excel regularly, chances are you’ve used—or tried to use—VLOOKUP. One of the most popular and powerful functions in Excel, VLOOKUP enables you to find and retrieve data in lightning speed.
But as robust and amazing as VLOOKUP is, it has some quirks that make it difficult to use, and all too easy to get those dreaded #N/A errors—or incorrect data. Fortunately, the geniuses on the Microsoft Excel team have just released XLOOKUP, a brand-new function available in Office 365* that replaces VLOOKUP. (It also replaces HLOOKUP, the lesser-used function for searching horizontally, in spreadsheet rows.)
You're not meme-level excited yet? Here are the game-changing differences.
Fewer steps that are easier to remember. VLOOKUP required four arguments (or elements of the function), and the order of them wasn’t intuitive. XLOOKUP requires just three steps and they follow a logical, easy-to-remember order. (There are additional, optional steps—for instance, if you aren’t looking for an exact match—but most users will get by with the regular three-step XLOOKUP.)
No more counting column numbers. VLOOKUP required you to input the column number of the data you wanted returned. This step was clunky, and the formula could get messed up if a new column was inserted into the lookup array. XLOOKUP doesn’t rely on column numbers, which saves you a step and reduces the potential for error.
No need to rearrange columns. VLOOKUP could only search to the right of a reference column, which meant users often had to reformat their data before working with it. XLOOKUP, by contrast, can look right, left, up, or down. Less time formatting data = quicker results.
To see XLOOKUP in action, check out this video from Excel Essential Training (Office 365) with Dennis Taylor:
Now that XLOOKUP is here, it’s going to be a whole lot easier to be an Excel power user. To brush up on your Excel skills, check out these LinkedIn Learning courses:
Excel Quick Tips with Garrick Chow, Nick Brazzi, and Jess Stratton
Excel: Macros and VBA for Beginners with Chris B. Smith
Excel: PivotTables for Beginners with Dave Ludwig
Excel: You Can Do This with Oz du Soleil
Excel: Creating a Basic Dashboard with Curt Frye
* XLOOKUP is currently only available to those on the Office Insider Ring, but Microsoft will make it more broadly available to Office 365 subscribers later this year.