XLOOKUP vs. VLOOKUP

XLOOKUP vs. VLOOKUP: Is it Worth Learning?

3 Oct 2024

For experienced users of excel VLOOKUP has been a crucial tool for data analysis. In 2019 XLOOKUP was released with many users preferring the simplicity the new LOOKUP brought, but is XLOOKUP worth the effort of learning for those already comfortable with VLOOKUP? 

 

VLOOKUP has been with Excel since the beginning in 1985. Since its introduction it has been a staple for Excel users, enabling users to quickly extract impactful information from large datasets. 

Despite its usefulness in data management VLOOKUP still had its limitations. In response to users’ frustrations, XLOOKUP was released as a more user-friendly function that would ensure the same valuable insights could still be captured. 

In this post we will explore the main differences and similarities between the two functions, and why XLOOKUP might be the upgrade you’re looking for. 

 

Does XLOOKUP Work with All Versions of Excel?

Let’s start off simple. If you want a universal LOOKUP, VLOOKUP has got your back. 
XLOOKUP is only available in Excel 2021 and Microsoft 365 so if you are using an older version VLOOKUP is the one for you. 

However, if you use a range of versions, and you want to know a LOOKUP that will always work VLOOKUP will still work with the newer versions too. 

 

Does XLOOKUP Work with Google Sheets?

 

With the rise in online collaboration many individuals and businesses have moved to Google Sheets for its ease of use and accessibility, especially its availability to free versions.

Google sheets is continually expanding to match Excel’s functionality and recognises the majority of functions used by Excel.

If you are already comfortable using VLOOKUP in Google Sheets, there’s no need to worry as XLOOKUP is also supported within Google Sheets.

 

Why Use XLOOKUP Instead of VLOOKUP?

 

For users who already use VLOOKUP, they might think there is not a need to learn a new LOOKUP. 

VLOOKUP is a widely known and recognised function, there is an abundance of tutorials on how to use it. 

This could be an advantage for VLOOKUP as when teaching new users a LOOKUP there will be more materials readily available, and the experienced staff will feel more comfortable offering guidance. 

 

Fewer Formulas to Learn

 

One of the great features of XLOOKUP is that it replaces both VLOOKUP and HLOOKUP.

Although VLOOKUP is well known, HLOOKUP is less familiar meaning that some users might be less comfortable when working with horizontal data.

With XLOOKUP you don’t need to worry about the direction of the data, it handles both vertical and horizontal data with a single formula. 

 

Is XLOOKUP Easy To Use?

 

On the face of it, XLOOKUP looks more complicated than VLOOKUP.  It has more arguments, which makes it look more intimidating. 

Let’s look at the syntax of both functions:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

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

But the square bracketed arguments are optional, so let’s compare the required arguments.

 

How is XLOOKUP Similar to VLOOKUP?

 

=VLOOKUP(lookup_value, table_array, col_index_num)

This means: what value am I looking for, in what table, and what column will my result be in.

 

=XLOOKUP(lookup_value, lookup_array, return_array)

This means: what value am I looking for, where am I looking for it, where do I want to find the answer.

 

VLOOKUP assumes you remember it can only look up values in the leftmost column of the table array, where with XLOOKUP you can just give any array to search in and any array for returning results. 

 

VLOOKUP & XLOOKUP Optional Arguments

 

The only optional argument VLOOKUP has is [range_lookup], the options for this are TRUE -Approximate match or FALSE - exact match.

XLOOKUP has a similar optional argument [match_mode], however the options offer much more flexibility:

• - Exact match
• - Exact match or next smallest item
• - Exact match or next largest item
• - Wildcard character match

 

XLOOKUP is easier to use here for a few reasons:

 

1. The arguments are better labelled making it easier to understand their purpose.  [range_lookup] doesn’t refer to the type of match, so you need to remember what this is referring to. [match_mode] is much more clear, what type of match do I want?

2. As these are optional arguments,  Excel must make an assumption of what match type you want if you leave the argument blank. VLOOKUP assumes an approximate match, XLOOKUP assumes an exact match. Exact matches are far more commonly used so it makes sense to assume an exact match unless told otherwise.

3. The options aren’t described as clearly in VLOOKUP either! “Approximate match” you might think will search for the closest match to the search value but it actually means if it can't find the exact value you're looking for, it will return the closest value that is less than or equal to it. The XLOOKUP descriptions are much more specific in their descriptions, reducing confusion.

 

What are XLOOKUP’s other optional arguments?

 

[if_not_found]

XLOOKUP has an optional argument of [if_not_found] which VLOOKUP doesn’t have. 

This means that should you search for a value which doesn’t exist VLOOKUP will bring up an #N/A error.

To counteract this, you have to nest the VLOOKUP inside an IFERROR or IFNA function, adding complexity to your formula. 

Nested formulas often lead to mistakes. XLOOKUP’s [if_not_found] argument allows you to specify a custom message or value to return when a search value isn’t found, eliminating the need for complex nested formulas.

 

[search_mode]

XLOOKUP’s last optional argument is [search_mode] which clarifies which order to search your data in.

This leads to better data retrieval and analysis as it eliminates the need for additional steps to retrieve the most relevant data.

For example, if you were looking for a most recent complaint, you could use a backward search to quickly retrieve the necessary data without the need for multiple formulas. 

 

When to use XLOOKUP Instead of VLOOKUP

 

As VLOOKUP can only search for values to the right of the lookup column, if the lookup array is not the leftmost column, it can be easier to use VLOOKUP.

In the below example, it would be impossible to perform a VLOOKUP to find a salary without rearranging the data, which can be time consuming and impractical.

On the other hand XLOOKUP can perform the process with ease!

 

 

Conclusion: Is it better to use XLOOKUP than VLOOKUP?

 

XLOOKUP is better than VLOOKUP. If you weigh up the advantages and disadvantages of both VLOOKUP just doesn’t compare.

The real question is, is XLOOKUP worth learning if I am already comfortable with a tool which can do most of the same thing?

I would argue that it absolutely is, it’s easy to pick up thanks to its intuitive design, so it will ultimately save you time and frustration.

VLOOKUP, it’s time to move aside and make room for the future of data lookup functions. 

If you want to learn more about LOOKUPS and other functions of Excel, take a look at our Advanced Excel Course