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

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).
To summarize the benefits:

• 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