XLOOKUP() vs. VLOOKUP(): A Comparison for Excel Users

XLOOKUP() and VLOOKUP() Syntax

Now that you have a basic understanding of both functions, let’s understand their syntax and how to use them in real-life applications. 

Syntax of VLOOKUP()

Let’s take a look at the VLOOKUP() syntax. 

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

In the above formula:

• lookup_value is the value you want to search for. This can be a number, text, or a reference to a cell containing the search value.

• table_array is the range of cells that contains the data. The first column in this range should contain the search_key.

• col_index_num is the column number in the range from which you want to retrieve the value. 

• range_lookup is where you enter a logical value (TRUE or FALSE).

Syntax of XLOOKUP()

Let’s now take a look at the xlookup() syntax. As we can see, XLOOKUP() has more arguments than VLOOKUP().

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

In the above formula:

• lookup_value is the value you want to search for.

• lookup_array is the range of cells where the lookup value is searched.

• return_array is the range of cells that contains the value you want to return.

• if_not_found (optional) returns a value if no match is found. If the value is not specified, it returns #N/A.

• match_mode (optional) determines the type of match to perform. 0 is for exact match (default). -1 is for the exact match or the next smaller item. 1 is for the exact match or the next larger item. 2 is for the wildcard character match.

• search_mode (optional) determines the search mode to use. 1 is used to search from first to last (default). -1 is used to search from last to first. 2 is used to perform binary search in ascending order. -2 is used to perform binary search in descending order.

Main Differences: XLOOKUP() vs. VLOOKUP()

Let’s now explore some key differences between the XLOOKUP() and VLOOKUP() functions.

Exact match mode 

XLOOKUP() defaults to exact match mode but VLOOKUP() requires specifying FALSE for an exact match.

Exact match difference between XLOOKUP() and VLOOKUP(). Source: Image by Author.

In VLOOKUP(), I didn't specify the range_lookup value (4th argument), so it gave the closest match, which is not our desired result. XLOOKUP(), on the other hand, returns the exact match by default.

Lookup direction

VLOOKUP() is restricted to searching only to the right of the first column in the selected table array. But XLOOKUP() can search for values in any direction.

Lookup direction difference between VLOOKUP() and XLOOKUP(). Source: Image by Author.

Here, VLOOKUP() didn’t find grades because it can only search for data to the right of the lookup_value (student name). In comparison, XLOOKUP() finds the grades for Robin because it can search in either direction (left or right).

Array references

In VLOOKUP(), you have to define the entire data range (table array) and specify the column number (column index) containing the desired output (return column) within a single formula. XLOOKUP() is more flexible than this. It allows you to define separate arrays for the lookup value and the data you want to return. 

Array references between VLOOKUP() and XLOOKUP(). Source: Image by Author.

Horizontal lookup

VLOOKUP() doesn't allow you to perform horizontal searches. If you want a horizontal search, you would have to use HLOOKUP() instead. XLOOKUP() consolidates these two functions because it performs both vertical and horizontal lookups so you don’t have to use separate functions for different lookup directions.

Horizontal lookup difference between VLOOKUP() and XLOOKUP(). Source: Image by Author.

Handling column insertions/deletions 

The column changes affect VLOOKUP() because of the hardcoded column_index_num. However, XLOOKUP() isn't affected by column changes. It continues functioning without adjustments to the formula.

XLOOKUP() remains unaffected, while VLOOKUP() fails. Source: Image by Author.

Sorting and searching

You can sort VLOOKUP() in ascending order, but this would lead to difficulties when dealing with unsorted data. However, XLOOKUP() can use the search_mode argument to search in both ascending and descending order.

Handling Insertion/deletion in XLOOKUP() and VLOOKUP(). Source: Image by Author.

Custom error messages

VLOOKUP() shows an #N/A error sign when no match is found. XLOOKUP() has an optional if_not_found parameter that lets you customize the output text if a value is not found. 

Customizing error messages in XLOOKUP() and VLOOKUP(). Source: Image by Author.

You can see VLOOKUP() couldn't find the student in its list and displayed a #N/A error. Similarly, the XLOOKUP() function could not locate the student's name. However, unlike VLOOKUP(), XLOOKUP() returns a specific message when there's no match.

Return multiple values

VLOOKUP() can return only one value at a time. But XLOOKUP() can retrieve values from multiple columns simultaneously. That’s why you can use one XLOOKUP() formula instead of multiple VLOOKUP() formulas.

Returning multiple values with XLOOKUP() and VLOOKUP(). Source: Image by Author.

Search mode

VLOOKUP() looks through a list from the beginning and only returns the first value that matches what you want. But XLOOKUP() can scan a list in either direction (top-down or bottom-up) and find things quickly in extensive lists.

Difference between VLOOKUP() and XLOOKUP(). Source: Image by Author.

Here, VLOOKUP() retrieves the first occurrence, which is from the first semester. However, with XLOOKUP(), I use the search mode code -1 to search from the bottom up.

Practical Examples and Use Cases

Let’s now take a look at some practical examples and use cases.

Using a lookup without having to re-arrange a table

I have a list of students' names and scores, and I want to find each student's score based on their name. For this purpose, I’ll use both XLOOKUP() and VLOOKUP() functions to show you the differences. 

When using XLOOKUP(), I enter the following formula:

=XLOOKUP(D8,B2:B5,A2:A5)

In this formula:

• D8 contains the look up value which is Charlie.

• B2:B5 range contains the student names. The XLOOKUP() function will search for the value in D8 within this range.

• A2:A5 is the range that contains the scores corresponding to the student names. 

When using VLOOKUP(), I enter the following formula:

=VLOOKUP(D4,A1:B5,1,0)

In this formula:

• D4 contains the look up value which is Charlie.

• A1:B5 range searches within all the columns (Scores, Student Name).

• 1 returns the value from the 1st column of the range (Scores).

• 0 retrieves the exact match.

VLOOKUP() returns an #N/A error but XLOOKUP() handles it. Source: Image by Author.

As you can see, VLOOKUP() shows an #N/A error because it cannot search to the left.

Creating a custom output when a value is not found

I have a list of students with their IDs, names, and grades. While searching, I accidentally entered a student ID that was not on the list. To handle such a case, I want a customized message to appear whenever the student ID is not found. I’ll use both XLOOKUP() and VLOOKUP() functions to show you the differences. 

When using XLOOKUP(), I enter the following formula:

(=XLOOKUP(E9, A2:A5, B2:B5, "Student not Found")) 

In this formula:

• E9 contains the value we want to search for. In my case it's 14256.

• A2:A5 is the cell range containing the look-up value (Student ID).

• B2:B5 searches for the score of the ID 14526.

• Student not Found displays if the score is not on the list. 

When using VLOOKUP(), I enter the following formula:

(=VLOOKUP(E6, A2:C5,2,0)) 

In this formula:

• E6 refers to Student ID, which is what am searching for.

• A2:C5 refers to the range which contains all the data in the table.

• 2 indicates the column number from which the data will be fetched.

• 0 retrieves the exact match.

XLOOKUP() returns a message but VLOOKUP() doesn’t. Source: Image by Author.

You can see 14256 does not exist in the range A2:A5, so XLOOKUP() returns the custom message Student not found. And VLOOKUP(), on the other hand, throws an #N/A error. 

Searching from the bottom-up

I want to find Sarah's salary from the most recent year. For this purpose, I’ll use both XLOOKUP() and  VLOOKUP() functions to show you the differences. 

When using XLOOKUP(), I enter the following formula:

=XLOOKUP(F8,B2:B10,C2:C10,,,-1)

In this formula:

• F8 contains the lookup value Sarah.

• B2:B10 looks in the range and searches for Sarah. 

• C2:C10 retrieves the data.

• -1 searches from the bottom of the column. 

When using VLOOKUP(), I enter the following formula:

=VLOOKUP(F4, B2:C10,2,0)

In this formula:

• F4 contains the lookup value Sarah.

• B2:C10 searches for the lookup value within this range.

• 2 indicates the column number Salary from which the data will be fetched.

• 0 retrieves the exact match.

Difference between VLOOKUP() and XLOOKUP() in search mode. Source: Image by Author.

You can see XLOOKUP() finds the desired salary for the desired year, whereas VLOOKUP() only displays the first matched value. I was able to do this because XLOOKUP() allows me to search form the bottom-up.

Performance and Compatibility

Let’s now compare the performance and compatibility of both functions. 

Performance in large datasets

VLOOKUP() can be slower when dealing with large datasets, especially if the lookup column is not sorted and the range lookup argument is set to FALSE for an exact match. This is because VLOOKUP() scans the dataset sequentially until it finds a match, which can be time-consuming for large tables. XLOOKUP() offers better performance with large datasets. It can handle exact matches more efficiently and search horizontally and vertically without sorting. 

Compatibility with different versions of Excel

Now, you must be thinking of ditching VLOOKUP() after seeing the benefits of XLOOKUP(). But let me clarify—although XLOOKUP() is a much faster and excellent feature, there are a couple of times when it’s unavailable. This is because XLOOKUP() is a newer feature that only works in Excel 2021 and Microsoft 365 (from 2019). If you collaborate with people using older versions of Excel, you’ll need to use VLOOKUP() and other functions instead of XLOOKUP().

Summary table

Let's create a summary table for easy reference. 

Final Thoughts

XLOOKUP() outperforms VLOOKUP(), especially when handling large datasets or complex lookup requirements. Its flexibility, including bidirectional search, multiple result retrieval, and adaptability to data changes, makes it a helpful tool. However, VLOOKUP() still holds its place, particularly for users of older Excel versions or those preferring a simpler approach to basic lookups. The best choice ultimately depends on your specific needs and Excel version.

If you're new to Excel or want to build a solid foundation, our Introduction to Excel course and the Excel Fundamentals skill track are great starting points for mastering the basics.