Last week, the team at Microsoft announced the XLOOKUP feature for Excel users, a successor to the VLOOKUP function, the first lookup function learned by Excel users. XLOOKUP feature gives Excel users an easier way of displaying information in their spreadsheets. Currently, this function is only available to Office 365 testers and the company will be making it more broadly available.
XLOOKUP has the ability to look vertically as well as horizontally and it replaces HLOOKUP too. XLOOKUP just needs 3 arguments for performing the most common exact lookup whereas VLOOKUP required 4.
The official post reads, “Let’s consider its signature in the simplest form:
- lookup_value: What you are looking for
- lookup_array: Where to find it
- return_array: What to return”
XLOOKUP overcomes the limitations of VLOOKUP
Exact match in XLOOKUP is possible
VLOOKUP resulted in a default approximate match of what the user was looking for, rather than the exact match. With XLOOKUP users can now find the exact match.
Data can be drawn on both sides
VLOOKUP can draw on the data that’s on the right-hand side of the reference column, so users have to rearrange their data to use the function. With XLOOKUP, users can easily draw on the data both to the left and right, and it also combines VLOOKUP and HLOOKUP into a single function.
VLOOKUP’s 3rd argument is the column number so if you insert or delete a column then you have to increment or decrement the column number inside the VLOOKUP. With XLOOKUP users can easily insert or delete columns.
Search from the back is now possible
With VLOOKUP, users need to reverse the order of the data for finding the last occurrence of the data but with XLOOKUP it is easy for users to search the data from the back.
References cells systematically
For VLOOKUP, the 2nd argument, table_array, needs to be stretched from the lookup column to the results column. It references more cells which results in unnecessary calculations, reducing the performance of your spreadsheets. XLOOKUP systematically references the cells which don’t lead to complications in calculations.
In an email to CNBC, Joe McDaid, Excel’s senior program manager wrote, XLOOKUP is “more powerful than INDEX/MATCH and more approachable than VLOOKUP.”
To know more about this news, check out the official post.
What’s new in application development this week?