XLOOKUP – The New Improved VLOOKUP

In Microsoft Excel, everyone has heard of the VLOOKUP function. It is one of the most used functions within Excel and the function that most people want to learn. This is not surprising, because VLOOKUP is extremely useful and can be used in a wide variety of situations. It is capable of very complex data analysis, but can also be used very simply. In essence VLOOKUP does what it says on the tin. It looks things up. So why have Microsoft, effectively, created a replacement for it?

Although VLOOKUP is used by millions of Excel users every day, it is not without faults. In fact, many Excel users use alternative formulas because of this. VLOOKUP has three main issues:

Only Left to Right

VLOOKUP only works from left to right. That is, it can only find a result value in a column to the left of the value that is being used as the look-up value. In the example below, if the Employee IDs were in a column right of the AGEs, it wouldn’t work.

The Column Number Issue

The column with the result required is designated by an index number. This means that if we want VLOOKUP to return data from the 2nd column of our data, we need to type in 2. The problem comes if another column is inserted into our data. Although our VLOOKUP will still have an index number of 2, the value we want to return may not be in the 2nd column anymore. At best, we won’t get the result we expect, at worst the VLOOKUP may stop working altogether.

VLOOKUP Defaults to an Approximate Match

LOOKUP functions in Excel can return an Exact match, meaning that they will only give a result if they can find precisely what we are looking for, or they can return an approximate match, meaning they will return the result nearest to the value we were searching for. Even though 99.9 % of the searches done with VLOOKUP are for an exact match, VLOOKUP default to an approximate match.

XLOOKUP fixes all these issues and more:

Left Lookup

VLOOKUP will not allow a left lookup to be performed. Before XLOOKUP, this could be done by using a combination of INDEX and MATCH functions, but this was quite complicated. Now a left lookup can be completed simply by using the XLOOKUP function.

The XLOOKUP function looks up the age and returns the Employee ID.

Column Number

XLOOKUP allows the returning of a value from a particular column simply by selecting it or typing its range within the formula. A column number need not be specified and so the formula can’t be broken if columns or rows are inserted or deleted.

Exact Match

By default, the XLOOKUP function in Excel 365/2021 performs an exact match, unlike VLOOKUP.

XLOOKUP is only available in Office 2021 and Office 365. VLOOKUP will remain available in Excel, but there are many more benefits in using XLOOKUP, such as being able to return values from above and below, as well as from left and right. To find out more and to learn how to use this new function fully, why not try one of our Excel courses.

0 Responses to “XLOOKUP – The New Improved VLOOKUP”

  1. No Comments

Leave a Reply