How to Use the XLOOKUP Function in Microsoft Excel
Excel’s new XLOOKUP will replace VLOOKUP, providing a powerful replacement to one of Excel’s most popular functions. This new function solves some of VLOOKUP’s limitations and has extra functionality. Here’s what you need to know.
What is XLOOKUP?
The new XLOOKUP function has solutions for some of the biggest limitations of VLOOKUP. Plus, it also replaces HLOOKUP. For example, XLOOKUP can look to its left, defaults to an exact match, and allows you to specify a range of cells instead of a column number. VLOOKUP is not this easy to use or as versatile. We’ll show you how it all works.
For the moment, XLOOKUP is only available to users on the Insiders program. Anyone can join the Insiders program to access the newest Excel features as soon as they become available. Microsoft will soon begin to roll it out to all Office 365 users.
How to Use the XLOOKUP Function
Let’s dive straight in with an example of XLOOKUP in action. Take the example data below. We want to return the department from column F for each ID in column A.
This is a classic exact match lookup example. The XLOOKUP function requires just three pieces of information.
The image below shows XLOOKUP with five arguments, but only the first three are necessary for an exact match. So let’s focus on them:
- Lookup_value: What you are looking for.
- Lookup_array: Where to look.
- Return_array: the range containing the value to return.