VLOOKUP will soon be replaced by a new Excel function XLOOKUP. VLOOKUP had its own limitations and keeping in mind Microsoft has introduced a powerful replacement to one of Excel’s most popular functions. Not only this functions removes the limitations of VLOOKUP but at the same time has many added features.

What is XLOOKUP?

XLOOKUP can be termed as completely refined version of VLOOKUP but without the limitations of VLOOKUP. The new XLOOKUP function has answers for some of the long unsolved limitations of VLOOKUP. Added bonus, it also replaces HLOOKUP.

Why is it better than VLOOKUP?

VLOOKUP cannot look to its left, XLOOKUP can. This function can default to an exact match. XLOOKUP allows you to specify a range of cells rather than a column number. VLOOKUP’s limitations made it difficult to use and non-versatile.

As of now, XLOOKUP function is only available to users on the Insiders program. To join the program click on Insiders program. After joining you will be able to use the latest Excel features as soon as they become available. Microsoft will soon roll out the feature to all Office 365 users.

How to Use the XLOOKUP Function

Let’s start with simple example of XLOOKUP. Take the sample data below. We want to return the branch from column F for each Code in column A.

This is a exact match lookup sample. Beauty of the XLOOKUP function is that it requires just three pieces of information.

The image below shows XLOOKUP with five arguments, but only the first three are needed for an exact match. So let’s see how it works out:

• Lookup_value: What you are looking for.
• Lookup_array: Where to look.
• Return_array: the range containing the value to look for.

The following formula will work for this example: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

XLOOKUP has multiple add on features that makes it superior to VLOOKUP, let’s have a quick look at them here.

XLOOKUP no more needs Column Index Number

The notorious third argument of VLOOKUP was to specify the column number of the data to return from a table array. This is no longer a hurdle as XLOOKUP enables you to select the range to return from.

XLOOKUP can Look data to the LEFT

XLOOKUP can view the data left of the selected cell, unlike VLOOKUP. One of the biggest advantage over VLOOKUP.

You will no longer have the issue of a broken formula whenever a new column is inserted the return range would adjust automatically in the spreadsheet. A big relief from re-verification after every small change.

VLOOKUP was limited in its functionality by searching the left-most column of a table and then returning from a specified number of columns to the right. In XLOOKUP function, the order of the table columns does not matter.

XLOOKUP gains an upper hand over VLOOKUP.

Exact Match is the new Default

VLOOKUP gave a headache when, you always had to specify that an exact match was wanted.

XLOOKUP fortunately gives a big relief by making exact match as the default – formulae are meant to give exact match, aren’t they? Now, this reduces the need to specify that fourth argument as ‘True’ or ‘False’ and ensures fewer mistakes by users who are new to the formula.

In short, XLOOKUP asks less than VLOOKUP, but gives more results than VLOOKUP, and its more user friendly and accurate.

XLOOKUP Replaces not only VLOOKUP but also the HLOOKUP Function

The XLOOKUP function is pretty serious about it job and has hit two target with one arrow. Not only VLOOKUP but HLOOKUP is approaching the sunset clause. A stupendous job done!

The HLOOKUP function, used for searching along rows, also called the horizontal lookup.

Not as popular as its sibling VLOOKUP or vertical lookup, but pretty useful function too.

XLOOKUP is a well thought name as it can look in both directions – down columns and also along rows. Need for two different functions no longer needed.

Also read: Inexpensive 3D Homes, Innovative Turbine Gadget that will charge you phone using water/wind,

Another feather to XLOOKUP, as it can Look from the Bottom-Up

In general, you need to hunt down a list to find the first instance of a value. XLOOKUP has flexibility to add a fifth argument viz. search mode. This enables us to switch the lookup to start at the bottom and look up a list to find the last instance of a value instead.

XLOOKUP for a Range Lookup

Although not as popular as the exact match, very effective use of a lookup formula is to look for a value in ranges.

To wind up

The XLOOKUP function is the de facto successor to both the VLOOKUP and HLOOKUP functions. It not only incorporates all their functionality but also removes their limitations, making it not only versatile but also durable and user friendly.

XLOOKUP can be used across sheets, workbooks and also with tables is something worth giving a look as dynamic arrays being introduced into Excel soon, it can also return a range of values.

VLOOKUP and HLOOKUP functions are here with an expiry date approaching at a fast pace with XLOOKUP being crowned the new king of excel functions.