Excel finally has a more intuitive and useful search function, with the new XLOOKUP tool now generally available for Microsoft’s 200 million-plus Office 365 users.
XLOOKUP replaces VLOOKUP (described by Microsoft a touch excitedly as “iconic”); a tool that is the third most-used function in Excel, after SUM and AVERAGE.
The latter’s flaws are well documented and include defaulting to an “approximate” match rather than an exact match. VLOOKUP also cannot look to the left: it always searches the first column, then returns a column to the right. There is no way to return values from a column to the left, forcing users to rearrange their data.
What Does XLOOKUP Do?
Microsoft said: “XLOOKUP helps users find what they need more efficiently with fewer limitations, from being able to look up a value vertically and horizontally (and to the left!) to supporting column insertions and deletions and more.” (The company had teased an early look at the new tool for Windows insiders in August 2019.)
Users can tap the XLOOKUP function when they need to find things in a table or a range by row. As Microsoft notes: “For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.”
Microsoft claims well over a billion Microsoft Office users. The precise number of Excel users is hard to come by, but has been estimated at two-thirds of Office users.
When Do I Get It?
Many, however, will be waiting for a while to get XLOOKUP (they’ll need Office 365: users of Excel 2010/2013/2016/2019 are out of luck).
Those with it, however, are raving. As one Reddit user put it this week: “This is completely different [to INDEX/MATCH and a total game changer…
“I’ve been playing with it all day and it’s absolutely amazing. Throw a sum around it and now your sumifs are gone (given it can return arrays and can search on multiple criteria across multiple columns. Can Index/Match check 7 different criteria across 7 different columns for a match? That’s a nightmare without XLOOKUP…”
Users on the semi-annual upgrade cycle Office 365 will have to wait until July. Switching to the monthly upgrade cycle will allow users to play now. (If Excel -> File -> Account “About Excel” shows ‘semi-annual channel’, the wait will be until July…)
