Microsoft announces XLOOKUP for Excel users that fixes most VLOOKUP issues

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:

XLOOKUP(lookup_value,lookup_array,return_array)

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.

Column insertions/deletions

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.