XLOOKUP vs VLOOKUP in Excel (Comparative Analysis)

In Microsoft Excel, you might be well aware of the VLOOKUP function whereas the addition of the XLOOKUP function in Excel 365 has a greater possibility to surpass the former one considering the user-friendliness. In this article, you’ll have a detailed overview of the comparisons between these two useful lookup functions: XLOOKUP and VLOOKUP with suitable examples and appropriate illustrations.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Basics of XLOOKUP and VLOOKUP in Excel

XLOOKUP Function

xlookup function syntax in excel

The XLOOKUP function searches a range or an array for a match and returns the corresponding item from the second range or array. By default, an exact match is used. The generic formula of this function is as follows:

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


VLOOKUP Function

vlookup function syntax in excel

The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from a specified column. By default, the table must be sorted in ascending order. The generic formula of this function looks like this:

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


Arguments Explanation of XLOOKUP Function

Argument Required/Optional Explanation
lookup_value Required The specified value that is to be searched for in the data table.
lookup_array Required A range of cells or an array where the lookup value will be searched for.
return_array Required The second range of cells or an array from where the output data will be extracted.
[if_not_found] Optional Customized message in a text format, if the lookup value is not found.
[match_mode] Optional It defines if the function will look for an exact match based on specified criteria or a wildcard character match.
[search_mode] Optional It defines the search order (In ascending or descending, from last to first or first to last).

Arguments Explanation of VLOOKUP Function

Argument Required/Optional Explanation
lookup_value Required The specified value that is to be searched for in the data table.
table_array Required A range of cells or an array where the lookup value will be searched for.
col_index_num Required The index number of the column in the specified array, where the return value is present.
[range_lookup] Optional Defines the exact or approximate match.

5 Comparative Examples between the Uses of XLOOKUP and VLOOKUP

Let’s go through some examples now to find out how these two functions are similar or different from each other. You’ll also get to know where you should use the VLOOKUP or XLOOKUP function while working with your dataset.

1. XLOOKUP and VLOOKUP to Lookup Unique Value and Extract Data

In the following picture, a dataset is presented with several popular smartphone devices and their specifications. By using VLOOKUP and XLOOKUP functions here separately, we’ll see how these functions work out to extract unique data based on a specified lookup value.

For example, we’re going to extract the specifications of the Samsung S21 Ultra from the data table.

 XLOOKUP and VLOOKUP to Lookup Unique Value and Extract Data

We’re going to apply the VLOOKUP function first. In the output Cell C16, the required formula will be:

=VLOOKUP(B16,B5:E13,{2,3,4},FALSE)

After pressing Enter, you’ll get all the information for the specified smartphone available in the table.

 XLOOKUP and VLOOKUP to Lookup Unique Value and Extract Data

Now, if we use the XLOOKUP function instead of the VLOOKUP function, the output Cell C16 will be embedded by the following formula:

=XLOOKUP(B16,B5:B13,C5:E13,,0)

After pressing Enter, you’ll get a similar result as found with the VLOOKUP function.

 XLOOKUP and VLOOKUP to Lookup Unique Value and Extract Data

So, the basic difference between using these two functions is the VLOOKUP function has extracted multiple values based on the specified column numbers in an array whereas, the XLOOKUP function has returned a similar output by taking a range of cells containing the specifications as return array argument.


2. VLOOKUP is Unable to Show Message If Lookup Value Is Not Found

Now let’s think of a scenario where the lookup value has not been found in the data table. So, the use of the  VLOOKUP function will return a #N/A error here. But the XLOOKUP function will let you customize an output message if the lookup value is not found in the table.

Assuming that we’re going to find the specifications of the Xiaomi Mi 10 Pro in the following table.

VLOOKUP is Unable to Show Message If Lookup Value Is Not Found

As the lookup value is lying in Cell B16, the required formula with the VLOOKUP function in the output Cell C16 will be:

=VLOOKUP(B16,B5:E13,{2,3,4},FALSE)

After pressing Enter, the function will return a #N/A error as the lookup value is not available in the data table.

VLOOKUP is Unable to Show Message If Lookup Value Is Not Found

Now if you customize a message with the XLOOKUP function if the lookup value is not found then the required formula in Cell C16 could look like this:

=XLOOKUP(B16,B5:B13,C5:E13,"Not Found",0)

After pressing Enter, the function will return the specified message: “Not Found”.

VLOOKUP is Unable to Show Message If Lookup Value Is Not Found

If you want to display a customized message with the VLOOKUP function, then you have to combine the IF function with the VLOOKUP function here.


3. VLOOKUP Searches for Value in the Leftmost Column Only

In the following picture, the dataset has been modified a bit. The column containing the device names has been shifted to the end of the data table. As we know, the VLOOKUP function searches for a lookup value in only the leftmost column in a table, now we’ll find out the output the function will return if we look for a value in the rightmost column in the table.

VLOOKUP Searches for Value in the Leftmost Column Only

The required function in Cell C16 will be:

=VLOOKUP(B16,B5:E13,{1,2,3},FALSE)

After pressing Enter, you’ll find a #N/A error as the return output. So, it’s now understood that while using the VLOOKUP function, you have to look for a value only in the leftmost column, otherwise, the function will not display the expected result.

VLOOKUP Searches for Value in the Leftmost Column Only

But the XLOOKUP function will not disappoint you in this case. With the use of the XLOOKUP function, you can search for a lookup value anywhere in the table by simply mentioning the range of cells or a lookup array.

So, to extract the available data from the table for the specified device present in Cell B16, the XLOOKUP function will come up with the following syntax:

=XLOOKUP(B16,E5:E13,B5:D13,,0)

After pressing Enter, you’ll be shown the extracted data for the specified smartphone device right away.

VLOOKUP Searches for Value in the Leftmost Column Only


4. Extract Data Based on the Last Occurrence with XLOOKUP Only

The XLOOKUP function pulls out data based on the last occurrence of a lookup value in the table. For example, we can find out which smartphone device in Column B is the last one that uses the Bionic chipset.

The required formula with the XLOOKUP function in Cell C16 should be:

=XLOOKUP(B16,C5:C13,B5:B13,,0,-1)

After pressing Enter, the function will return the corresponding device name at once.

Extract Data Based on the Last Occurrence with XLOOKUP Only

In the function, we have used the [search_mode] argument where ‘-1’ implies that the function will look for the value from the last to the first. If you opt to choose ‘1’ here, then the function will look out from the first to the last.

On the contrary, the VLOOKUP function itself is not able to extract the data based on the last occurrence in a table. It might have to be combined with some other functions to look up the value from the last in the data table.


5. XLOOKUP Intakes An Optional Argument to Use Wildcard Characters

In our final example, we’ll explore how the VLOOKUP and XLOOKUP functions work while extracting data based on a partial match as the lookup value. For example, we’re going to find out the available specifications by searching for the model number ‘S21’ of a smartphone device.

XLOOKUP Intakes An Optional Argument to Use Wildcard Characters

With the uses of Wildcard Characters as well as Ampersand (&) operators, the VLOOKUP function in the output Cell C16 will look like this:

=VLOOKUP("*"&B16&"*",B5:E13,{2,3,4},FALSE)

After pressing Enter, the formula will return all the specifications for Samsung S21 Ultra from the given dataset.

XLOOKUP Intakes An Optional Argument to Use Wildcard Characters

Now while using the XLOOKUP function, we have to activate the [match_mode] argument and define it with ‘2’ to denote the Wildcard Characters matches.

So, the required function in Cell C16 will look like this:

=XLOOKUP("*"&B16&"*",B5:B13,C5:E13,,2)

After pressing Enter, you’ll get a similar result as found with the VLOOKUP function previously.

XLOOKUP Intakes An Optional Argument to Use Wildcard Characters


Why Is XLOOKUP Better Than VLOOKUP?

  • In the XLOOKUP function, you have to specify a range of cells or an array as the return array which is way too easier to denote from the data table. In the VLOOKUP function, while extracting multiple values from the table, you have to manually specify the index numbers of the columns in an array where the return values are present, and sometimes it’s quite tricky to find the index numbers from a large dataset.
  • The XLOOKUP function is handier when you have to show a customized message if the lookup value is not found. The VLOOKUP function cannot show any customized message itself.
  • The VLOOKUP function looks for a value in the leftmost column in a table whereas the XLOOKUP function looks for a value in any column in the given data table.
  • With the VLOOKUP function, you have to specify the entire table array where the lookup value and return value(s) are present. In the XLOOKUP function, you have to define the lookup array and the return array separately.
  • The XLOOKUP function searches a lookup value from the bottom to top in the given dataset whereas, the VLOOKUP needs other functions to extract data based on the last occurrence in the table.
  • The XLOOKUP function allows you to go for a binary search, the VLOOKUP function doesn’t come up with such criterion.
  • With the VLOOKUP function, you can use the approximate match to return the next smaller value only. But with the XLOOKUP function, you’ll be able to return any of the next smaller or next larger value from the table.

Limitations of XLOOKUP in Excel

There might be only one specific drawback with the XLOOKUP function and that is its availability in only Microsoft Excel 365. The XLOOKUP function is not compatible with older versions. But the VLOOKUP function is available to use in any version of Excel. So, it probably could be a better option to switch to the latest version of Microsoft Excel if you find the XLOOKUP function interesting to use.


Concluding Words

I hope that the comparative analysis between the two functions as well as the related examples illustrated above in this article will now help you to understand the differences between XLOOKUP and VLOOKUP in Excel. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


You May Also Like to Explore

VLOOKUP with Drop Down List in Excel

Range Lookup with VLOOKUP in Excel (5 Examples)

10 Best Practices with VLOOKUP in Excel

VLOOKUP to Return Multiple Values Horizontally in Excel

How to Use VLOOKUP If Cell Contains a Word within Text in Excel

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo