Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon

Microsoft announces XLOOKUP for Excel users that fixes most VLOOKUP issues

Save for later
  • 3 min read
  • 02 Sep 2019

article-image
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.

Unlock access to the largest independent learning library in Tech for FREE!
Get unlimited access to 7500+ expert-authored eBooks and video courses covering every tech area you can think of.
Renews at $19.99/month. Cancel anytime

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.

What’s new in application development this week?


Microsoft announces its support for bringing exFAT in the Linux kernel; open sources technical specs

Qt introduces Qt for MCUs, a graphics toolkit for creating a fluid user interface on microcontrollers

Twilio launched Verified By Twilio, that will show customers who is calling them and why