Xlookup vs Vlookup: Why Use One Over the Other?

Imagine yourself with a large data set in Microsoft Excel, and you need to find a particular value. If you don’t know the exact row and column it is in, it would take forever to find it. However, with Xlookup and Vlookup, your search is made easy.

You’re probably asking why not just use the good old CTRL + F combo to find your values, but these two provide more refined searches. Stay with us as we unpack the differences between these two.

When would you use Xlookup?

As the name suggests, Xlookup is a lookup function that returns the value of a specified cell or a range of cells. It is a relatively new function popularly known as the modern replacement of Vlookup and Hlookup. 

So, when was Xlookup introduced? Xlookup was introduced in 2019 after years of trial and error to correct the mistakes of its predecessors. 

For clarity, the x and v in the names are not related to the alphabet. Instead, they refer to the position of the column being searched on a spreadsheet. 

A Vlookup searches down the leftmost column, while an Xlookup searches down the rightmost column.

The two functions are similar in that they both search a list for a value and return another value based on whether or not the value is found. Their main difference is that their target columns are located in different positions within your spreadsheet.

They’re basically the same, only that Xlookup came with more improvements. Xlookup is a great way to get data from your data sources. It can be used in various ways, so it’s important to understand how it works and why it might be useful for you.

The choice of Xlookup vs Vlookup depends largely on how your data is arranged in Excel. If you have more than one column in your data table and only need to search from left to right like most people, then using Vlookup will probably be easier for you than Xlookup. 

However, if you need to search across multiple columns and your data isn’t arranged this way by default, Xlookup will be easier for you than Vlookup.

What are the main differences between Xlookup and Vlookup?

1. Syntax

Vlookup

The syntax formula for Vlookup is: (lookup_value,table_array,col_index_num,[range_lookup])

• Lookup_value – This is the value to look up in the search column. It can be a number, text string, or reference that matches the lookup value exactly.
• Table_array – The table array consists of values you want to search through.
• Col_index_num – The column number within the lookup table that contains the reference value. If your lookup table has multiple columns, specify which column you want to use.
• Range_lookup – This is an optional feature you can include depending on your desired results. If you want an exact match, you can include FALSE. This means that if there’s no match, the result will return an error instead of bringing the closest match.

For example, let’s say we have an Excel table of Windows Report employees and their phone numbers. The first column lists names and the second column has the person’s phone number.

We want to look up a person’s phone number based on their name. For example, if we enter Claire Moraa into cell A1, Excel should find Claire’s phone number in B1.

In this case, we’re using Vlookup because our data is organized in two columns, one containing names and one containing phone numbers.

Xlookup

The Xlookup syntax formula is: (lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

• Lookup_array – This is where the first element of the array will be searched.
• Return_array – This is an optional argument specifying whether to return the original value if it was not found in the array.
• If_not_found – Another optional parameter that determines what happens if there are no matches for your lookup query.  
• Match_mode – An optional parameter. It can be one of two values: 0 (default) or 1. A 0 match implies that all characters must match exactly, and 1 implies that characters only match approximately.
• Search_mode – Another optional parameter to specify the search mode whether from top or bottom.

As you can see, both syntaxes have different ways of handling searches. In an Xlookup function, if the search text is not found in the first column of your table, Excel will move down through each subsequent column until it finds a match or reaches the end of your table. 

On the other hand, Vlookup checks only one column at a time. The leftmost one when searching for matches. If it finds no matches in that column, it will stop searching and return no results.

Perhaps the greatest advantage of using Xlookup is multiple criteria for searches.

2. Error handling

From the syntaxes above, you can see that Xlookup has a lot of parameters that allow for built-in error handling. It’s not uncommon for the searches to return no results. In such cases, you’ll need to customize the syntax to find what you want.

The If_not_found parameter in Xlookup allows you to refine your search. For instance, in our example above, if we wanted to find another employee, but they are not on the list, the Vlookup syntax would return a N/A result.

However, using Xlookup, you can add an optional parameter using if_not_found and include Not on the list if the specified name cannot be found.

3. Reverse search

The Vlookup syntax is simple but limited. For the Xlookup, you can define where your search will begin. If you have a large data set and have an idea of where what you’re looking for, you can do a reverse search.

This basically means that you can start from the top or bottom to save time. Say we have a list of over 1000 employees, and you’re looking for a phone number, Vlookup only allows you to search starting from the left.

With Xlookup, you can start from the top or bottom.

In summary in tabulated form:

	Xlookup	Vlookup
Compatibility	Excel 2021 and later	All versions
Exact match	Yes	No
Optional parameters	Yes	No
Vertical/horizontal lookup	Yes	No

What is the disadvantage of Xlookup?

After reigning praises on Xlookup, you probably think it is too good to be true. Well, you’re not wrong. Xlookup is not without its faults, but hopefully, it won’t be significant enough to make you discard it.

Some of its known cons include:

• Compatibility – This is by far its ugliest con. Xlookup is only compatible with later versions of Excel starting from Excel 2021. Therefore, some users still on previous versions may be locked out of utilizing this function.
• Requires more resources – Any application performing such refined searches would require more than the usual system resources. In fact, one user is quick to point out that while using the Xlookup syntax, the results returned a N/A response.

On my work machine with 16 GB of RAM, I am getting XLOOKUPs that return ‘#N/A’ on values that exist. These same XLOOKUPs will update with the correct value as soon as I go to my dataset and ‘CTRL-F

While this is yet to be proven, there’s a slight chance it could be true. If this is the case, modern-day problems require modern-day solutions. You can always upgrade your RAM or use RAM cleaners.

Overall, it’s really up to you to decide which works best for your needs. In the end, both are valuable tools for Excel power users. While Vlookup may be more useful overall, it’s not as flexible or customizable as Xlookup.

If we were to decide, from where we sit, Xlookup looks like the function you should be using.

Have you interacted with any of these functions? We’d love to hear your feedback on which you prefer and why.