Analyst Cave

XLOOKUP vs VLOOKUP in Excel – Which is better and why?

The choice between using XLOOKUP vs VLOOKUP (or XLOOKUP vs INDEX MATCH if you prefer) in Excel will be an easy one it seems… Let me explain. The XLOOKUP function has been just recently announced as a new addition to the Excel functions base. With it came also other less known functions such as the XMATCH etc. This seems to end a decades old battle between fans of VLOOKUP vs INDEX MATCH. Even news sites caught this announcement as somewhat special. In this post we will understand the differences between both – at least based on the limited information we know.

What is all the hype about? To understand the differences of XLOOKUP vs VLOOKUP or XLOOKUP vs INDIEX MATCH it is best we compare the definitions of both formulas and explain using examples.

XLOOKUP vs VLOOKUP Example

In example below we are doing as simple lookup of Moscow against the C1:D6 table.

The formulas used:

=XLOOKUP("Moscow",C1:C6,D1:D6)
'( lookup value, lookup column, result column )  

=VLOOKUP("Moscow",C1:D6,2,FALSE)
'( lookup value, table array, index of result column, type of match )  

XLOOKUP Advantages

Key differences you should spot in the example above and appreciate if you are used to using the VLOOKUP formula (and a Excel geek like myself):

VLOOKUP Basics

If you want a quick recap of VLOOKUP – we can start with a reminder of the VLOOKUP function. The Excel VLOOKUP function allows you to find a row in a column that matches a certain value and returns a value from another corresponding column in that row. It works like a phone book e.g. find the name of a person and return his/her phone number.


The problem with the VLOOKUP was that:

XLOOKUP Basics

The XLOOKUP does the same operation as a VLOOKUP, however, is much more flexible even in its most basic version (and familiar to an INDEX MATCH combo).
To summarize the benefits:

XLOOKUP Syntax

We can also explore the full definition for the XLOOKUP function:

XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])

We already described the first 3 parameters however we also have 2 remaining:

XLOOKUP vs INDEX MATCH

XLOOKUP in fact is a combination of INDEX MATCH functions. The INDEX MATCH combo has been used often to counter the setbacks of using the VLOOKUP function – the required sequence of columns in a VLOOKUP (lookup column first) and the instability of VLOOKUP in case of column changes (if you want the full list read here). What however when compared to XLOOKUP?

Let us now look at a simple example of XLOOKUP vs INDEX MATCH:

The formulas used:

=XLOOKUP("Moscow",C1:C6,D1:D6)

=INDEX(D1:D6,MATCH("Moscow",C1:C6,0))

The clear winner is XLOOKUP again as being shorter while still keeping the same flexibility of the INDEX MATCH combo.

XLOOKUP Performance

Is the XLOOKUP function faster than VLOOKUP? Not that I noticed, even on large files – I would safely assume the performance differences even on large worksheets will be marginal. However, in terms of convenience and stability it beats VLOOKUP and INDEX MATCH by a lot.

If you are really struggling with your Excel performance maybe you are using the wrong tool for the job. Feel free to read my post on Excel VLOOKUP vs INDEX MATCH vs SQL vs VBA performance

XLOOKUP Disadvantages

Are there any disadvantages of using XLOOKUP? Albeit all the pros currently the main disadvantage is the fact that XLOOKUP is not backward compatible with older Excel versions (currently only for MS Office 365 subscribers). Your friends with older Excel version will not be able to use XLOOKUP and thus probably need to spend a lot of time refactoring your Excel file.

XLOOKUP – When in Excel?

XLOOKUP is already available for Microsoft Office 365 Subscribers. As per this article

Conclusions

Although XLOOKUP is a recent addition and many people still only recently announced I already see it as a final resolution to the problem of lookup operations in Excel. I am frankly much surprised it took Microsoft so long to introduce this function. If you are interested in how XLOOKUP will compare in performance to VLOOKUP stay tuned.

My surprising conclusion however is that for now probably… I will not be using the XLOOKUP formula often. Why? The problem is the fact that users of Excel 2010/2016 will not have this function available. If you collaborate with people with multiple versions of Excel I would recommend the same.

Exit mobile version