Excel XLOOKUP vs VLOOKUP vs INDEX MATCH

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.

Save time on VLOOKUPs with the Excel LOOKUP Wizard AddIn!

XLOOKUP vs VLOOKUP Example

To compare XLOOKUP and VLOOKUP let us use this example below. What we are doing below as simple lookup of Moscow against the C1:D6 table.

Both formulas will return the same result. Notice, however, for XLOOKUP we provided both the lookup column and the result column separately. While for VLOOKUP we needed to provide the whole table and indicate the result column number. The additional difference we see is that in XLOOKUP we didn’t have to provide the exact match parameter – in XLOOKUP the default is an exact match.

This makes the XLOOKUP function a combination of INDEX & MATCH functions. The VLOOKUP had a lot of issues like having to put the lookup column at the front of the table or at least before the result column.

In case needed here are 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):

XLOOKUP takes the lookup and result columns separately as arguments
For XLOOKUP we don’t need to specify the range_lookup parameter for exact matches i.e. True for exact match, False for approximation. This is because XLOOKUP assume exact matches by default
The XLOOKUP formula is simply shorter
Similarly to using the INDEX MATCH combo the XLOOKUP is safer as shifting columns will not break your formula

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:

The lookup_value needed to be in the first column of the table_array. Otherwise you would need to reorganize the columns
The col_index_num was not a reference on the number of the column in the table_array where the returned value can be found. Hence adding a column in between would cause the whole formula to return values from the incorrect column
The range_lookup was most of the time a useless argument, usually being set to FALSE (exact match)

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).

No longer do you need to worry about the lookup column being first in your table

You don’t have to worry about breaking any lookup formulas by adding or deleting columns from your table

You save time – as the basic version of XLOOKUP assumes you are looking for exact matches

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:

match_mode – the type of match you want to make. What is interesting is the mode 2 allowing you to use ? and * wildcards to replace a single or any number of characters in a lookup operation
search_mode – type and direction of search. Useful when there is a particular sorting order of data.

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?

XLOOKUP assumes exact matches by default vs MATCH function in which you need to put a 0 as last argument to assure exact matches
XLOOKUP is shorter than INDEX MATCH – thus saves you time and shortens your Excel formulas
XLOOKUP seems to be slightly faster than INDEX MATCH. This is probably as the implementation has been optimized within a single function vs the combination of 2 separate functions

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.

Related posts: