View all newsletters
Receive our newsletter - data, insights and analysis delivered to you
  1. Hardware
February 12, 2020

Excel Finally Gets the Search Function it Needs, with XLOOKUP General Availability

Look up a value vertically and horizontally (and to the left)...

By CBR Staff Writer

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).

Content from our partners
Green for go: Transforming trade in the UK
Manufacturers are switching to personalised customer experience amid fierce competition
How many ends in end-to-end service orchestration?

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…)

 

Topics in this article : , , ,
Websites in our network
Select and enter your corporate email address Tech Monitor's research, insight and analysis examines the frontiers of digital transformation to help tech leaders navigate the future. Our Changelog newsletter delivers our best work to your inbox every week.
  • CIO
  • CTO
  • CISO
  • CSO
  • CFO
  • CDO
  • CEO
  • Architect Founder
  • MD
  • Director
  • Manager
  • Other
Visit our privacy policy for more information about our services, how New Statesman Media Group may use, process and share your personal data, including information on your rights in respect of your personal data and how you can unsubscribe from future marketing communications. Our services are intended for corporate subscribers and you warrant that the email address submitted is your corporate email address.
THANK YOU