In this example, the formula is used to return the sales value relating to the name in cell A2. And often there is a logical reason for this. Users of lookup functions are very familiar with the #N/A error message that greets them when their VLOOKUP or their MATCH function cannot find what it needs. The following formula can achieve this: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8) In the example below, we need to lookup an ID (column E) and return the person’s name (column D).

VLOOKUP was constrained by searching the left-most column of a table and then returning from a specified number of columns to the right. With XLOOKUP, the order of the table columns does not matter. XLOOKUP can Look to the Leftīeing able to select a lookup range makes XLOOKUP more versatile than VLOOKUP. So in short, XLOOKUP asks fewer questions than VLOOKUP, is more user-friendly, and is also more durable. This reduces the need to answer that fifth argument and ensures fewer mistakes by users new to the formula. The XLOOKUP function requires just three pieces of information.įortunately, XLOOKUP defaults to an exact match-the far more common reason to use a lookup formula). This is a classic exact match lookup example. We want to return the department from column F for each ID in column A. Let’s dive straight in with an example of XLOOKUP in action. Microsoft will soon begin to roll it out to all Office 365 users. Anyone can join the Insiders program to access the newest Excel features as soon as they become available. We’ll show you how it all works.įor the moment, XLOOKUP is only available to users on the Insiders program. VLOOKUP is not this easy to use or as versatile.

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. The new XLOOKUP function has solutions for some of the biggest limitations of VLOOKUP. This new function solves some of VLOOKUP’s limitations and has extra functionality. Here’s what you need to know. Excel’s new XLOOKUP will replace VLOOKUP, providing a powerful replacement to one of Excel’s most popular functions.

Xlfn excel formula