XLOOKUP vs VLOOKUP: Top 5 Comparison of Excel Lookup Functions

XLOOKUP and VLOOKUP are Microsoft Excel functions used to search values in a list and return a related return. VLOOKUP has been a go-to option for many users but now XLOOKUP stands out as a highly versatile alternative to VLOOKUP. It allows you to quickly lookup values in a data set (just like VLOOKUP Excel) with additional flexibility and advantages.

In this article, we will cover XLOOKUP vs VLOOKUP in detail –

Download the Excel Workbook below to follow along and understand the comparison – XLOOKUP vs VLOOKUP –
download excel workbook VLOOKUP-vs-XLOOKUP-1.xlsx

 

#1 – Position of Lookup Value

VLOOKUP requires the lookup value to be on the leftmost column of the table whereas for XLOOKUP there are no such restrictions. XLOOKUP allows you to search for values in any column of the table range, making it much more flexible and adaptable to various data structures.

For example, you want to find the person’s first name based on their date of joining.

=VLOOKUP(G2,B2:D32,3,0)

=XLOOKUP(G6,D2:D32,B2:B32,0)

VLOOKUP provides an error because it is trying to search the lookup value i.e. 4/7/2022 in Column A. As it is not the leftmost column of the table array provided. To get the correct result, you will have to rearrange the table such that the lookup array i.e. DOJ is to the left of the resultant column i.e. First Name.

You can also use XLOOKUP to get the result because it does not require the lookup array to be on the left. Here, you need to individually provide the lookup array and return array in the formula. So, the arrangement of these columns in the table is irrelevant.

Let us understand how the XLOOKUP formula works here –

=XLOOKUP(G6,D2:D32,B2:B32,0)

where,

• G6 – This is the value you want to search for.
• D2:D32 – This is the array where Excel will search for the value you specified (G6).
• B2:B32 – This is the array from which Excel will return a corresponding value if a match is found.
• 0 – This is the last argument of the XLOOKUP function, which specifies the match mode. A value of 0 (zero) indicates an exact match.

So, Excel will look for 4/7/2022 in the DOJ column and then provide the corresponding result from the First Name column i.e. Lena.

 

#2 – Search Horizontally and Vertically

VLOOKUP is primarily designed for vertical searches i.e. it looks up for value in the leftmost column and returns a corresponding value from the specified column to its right. Whereas, XLOOKUP offers greater flexibility by allowing both vertical and horizontal searches.

Suppose, you want to get the first name for the employee ID 1004.

=VLOOKUP(H2,A1:E4,3,0)

=XLOOKUP(H6,A1:E1,A2:E2)

VLOOKUP will not be able to provide a correct result in this case because it will search for 1004 in Column A and since “1004” is not found there, VLOOKUP would fail to return the appropriate first name.

Let us understand how the XLOOKUP formula works here –

=XLOOKUP(H6,A1:E1,A2:E2)

where,

• H6 – This is the value you want to search for.
• A1:E1 – This is the array where Excel will search for the value you specified (G6).
• A2:E3 – This is the array from which Excel will return a corresponding value if a match is found.

XLOOKUP will find the value “1004” in the range of employee IDs (A1:E1) and retrieve the corresponding value “Josan” from the range of first names (A2:E2).

 

#3 – Search from Bottom to Top

One notable advantage of XLOOKUP over VLOOKUP is its ability to search from the bottom of the data range to the top. VLOOKUP only supports searching from top to bottom. This feature becomes handy when dealing with datasets organized in descending order.

Suppose, you want to know the date of joining of the last employee on the list whose name is “Lena”.

=VLOOKUP(G2,B2:D32,3,0)

=XLOOKUP(G6,B2:B32,D2:D32,,,-1)

VLOOKUP will also provide the first match in the list and return the result.

Whereas, XLOOKUP offers an option to specify the search mode as “last to first.” When using this mode, Excel will begin searching for the lookup value from the bottom of the data range and return the last matching result it encounters.

=XLOOKUP(G6,B2:B32,D2:D32,,,-1)

where,

• G6 – This is the value you want to search for.
• B2:B32 – This is the array where Excel will search for the value you specified (G6).
• D2:D32 – This is the array from which Excel will return a corresponding value if a match is found.
• Optional arguments [if_not_found] and [match_mode] are left empty.
• -1 – This argument specifies the search mode. -1 indicates that the search should be from last to first.

Hence, XLOOKUP searches for the lookup value Lena from the last and provides the corresponding result as 4/7/2022. Unlike, VLOOKUP which returns the first match i.e. 3/20/2022.

 

#4 – Match not found

VLOOKUP returns the #N/A error when it fails to find a match in the lookup array. While this is informative, it might not be the most user-friendly way to handle missing data. On the other hand, XLOOKUP allows users to customize the text displayed when a match is not found.

Suppose, you want to get the first name for the employee ID 1101.

=VLOOKUP(G2,A2:D32,2,0)

=XLOOKUP(G6,A2:A32,B2:B32,”Not Found”)

Since 1101 is not present in the table array, VLOOKUP will return an error. In XLOOKUP, you will get an option to add a text when a result is not found. Here, it will display “Not Found” as the result.

=XLOOKUP(G6,A2:A32,B2:B32,”Not Found”)

where,

• G6 – This is the value you want to search for.
• A2:A32 – This is the array where Excel will search for the value you specified (G6).
• B2:B32 – This is the array from which Excel will return a corresponding value if a match is found.
• Not Found – This argument specifies the if_not_found value. If a match is not found, Excel will display the result as “Not Found”.

 

#5 – Compatibility Issues

Even though XLOOKUP is a more versatile and useful function, users may encounter situations where it is vital to use VLOOKUP instead. XLOOKUP is accessible to users who have Microsoft 365 or Excel 2021. However, individuals using Excel 2019 or any earlier versions will not have access to the XLOOKUP function.

When sharing a file that contains XLOOKUP with a user who has an older version of Excel, they will suffer from compatibility issues. While they can view the results generated by the XLOOKUP formula in your file but modifying the formula will lead to a #NAME error. Hence, it is advisable to use VLOOKUP as it is available to all users irrespective of the Excel version they are using.

 

Conclusion

XLOOKUP and VLOOKUP are Excel functions used for searching values and returning related results. XLOOKUP offers more flexibility than VLOOKUP, allowing both vertical and horizontal searches, while VLOOKUP is limited to vertical searches only.

XLOOKUP also supports searching from the bottom to the top of the data range and provides customized text for unmatched values, but it may not be accessible to users with older Excel versions, leading to compatibility issues. In such cases, VLOOKUP remains a reliable option for all users.

Click here to know more about the XLOOKUP function in Excel.