Microsoft Excel geeks rejoice! The company has announced one of its most exciting releases in years – XLOOKUP. The newest feature gives more ways to display data in spreadsheets and tackles many of the limitations of an existing formula.
Isn’t VLOOKUP Good Enough?
The simple answer is no – VLOOKUP has restrictions like defaulting to the estimated match of what the user is looking for instead of the exact match. For the formula to work, you must move columns to the right. Beside it defaulting to the approximate match & the inability to look left, there are a few other limitations to VLOOKUP: users can’t search from the back, it references more cells the needed, doesn’t support column insertions/deletions, and cannot search for next larger item.
But guess, what? XLOOKUP fixes these common shortcomings.
To XLOOKUP & Beyond!
XLOOKUP is particularly interesting because not only does it act the same as VLOOKUP in that it retrieves data in vertical columns, it also leverages an existing function called HLOOKUP which works with horizontal rows (extra brownie points for those who knew that formula even existed)!
Impacts Digital Advertisers & Marketers
“For people like us in the digital field, we’ve worked around the restraints of VLOOKUP because it seemed powerful in retrieving the information we needed – until now,” said Marc Cerniglio, Manager of Insights and Automation at Chacka Marketing. He further commented…“The release of the XLOOKUP function will allow us to segment specific data sets that our clients wish to view without the need of rearranging our data or using multiple functions in tandem to accomplish it.”
The XLOOKUP Formula
Microsoft explained that XLOOKUP requires three arguments to accomplish the most common exact lookup (that’s one fewer than VLOOKUP):
- lookup_value: What you are looking for
- lookup_array: Where to find it
- return_array: What to return
Of course, there are more advanced variations of XLOOKUP if you’re particularly savvy; they can be found on the Microsoft tech community blog.
What Else You Should Know
As of now, XLOOKUP is in beta and available to select Office Insiders. Microsoft noted that over the next several months, the feature will be more widely released to all Office Insiders and Office 365 subscribers.
XMATCH was also introduced in the release. This function returns the index of the matching item. You can find more information on this formula at Support.Office.
Sources:
Spreadsheet jockeys are geeking out over this new Microsoft Excel feature, CNBC.com
Announcing XLOOKUP, Microsoft Tech Community
XLOOKUP function, Support.Office.com
XMatch Function, Support.Office.com
