XLOOKUP vs VLOOKUP Key Differences

Usage:

Both the XLOOKUP and VLOOKUP functions are used to find (lookup) a value from a range and return a related result. The XLOOKUP was introduced in 2019 as a successor to the VLOOKUP. Simply put, XLOOKUP is a more flexible and improved version of the VLOOKUP formula.

VLOOKUP 

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

Breakdown of the inputs (arguments):

• Lookup_value: the value you’re searching for.
• Table_array: the table or range you’re searching from.
• Col_index_num: an integer where you specify the column of the return value you want.
• [Range_lookup]: Optional feature where you can select TRUE or FALSE. TRUE means it’s an approximate match (meaning if no match, it returns the closest match), while FALSE means it’s an exact match (meaning if no match, it returns an error).

XLOOKUP

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

Breakdown of the inputs (arguments):

• Lookup_value: the value you’re searching for.
• Lookup_array: the range you want to use to find the matching value.
• Return_array: the range containing the corresponding result you want to return.
• [if_not_found]: Optional feature to customize the text if a match is not found.
• [match_mode]: Optional feature to specify the match type (exact match, next smaller item, etc.)
• [search_mode]: Optional feature to specify the search mode (starting with the first item on a list, last item on a list, etc.)

As you may have noticed in the formula syntax, the XLOOKUP offers a larger variety of optional inputs, which makes the formula much more flexible than a VLOOKUP. 

Now that we’re familiar with the syntax, let’s go over the key differences between them.

First Difference: Looking for values to the left of the lookup_array

XLOOKUP can look for values within columns to the left and right of the lookup_array, while VLOOKUP is limited to values on the right side of the first column in the selected table_array.

For example, let’s suppose we want to find the commission amount for Harley (a salesperson in Column C).

The VLOOKUP here gives us an #N/A (error sign) because it cannot look for the commission column (Column B) as it’s to the left of the salesperson column (Column C). On the other hand, the XLOOKUP can find the commission amount for Harley as it has the flexibility to find values to the left AND right of the lookup value column.

Second Difference: Customization when a valid match is not found

The XLOOKUP has an optional “if_not_found” feature which lets you customize your output text if a value is not found, while the VLOOKUP defaults to showing an #N/A (error sign).

For example, let’s suppose we want to find Alexis in the salesperson column (column C). However, Alexis is not on this list.

In this scenario, the VLOOKUP just gives us an #N/A (error sign) because it cannot find Alexis in the salesperson column. However, the XLOOKUP, (even though it also cannot find Alexis) lets us customize the text when a value isn’t found. In this case, we’ve written a “Not on the list” message for the scenario where any salesperson is not found. 

If you’re looking to learn more about lookup formulas, pivot tables, data analysis, data visualization, financial modeling, and other fundamental Excel skills, we recommend you check out our Excel for Business & Finance Course.

Third Difference: Specifying the search mode

The VLOOKUP doesn’t allow you to specify what type of search you want, while the XLOOKUP allows you to specify whether you want to start from the first or last item on a list, or in ascending or descending order.

For example, suppose you want to find out David’s commission in the most recent year (2023).

In this scenario, a VLOOKUP finds the first item, which is from the year 2019. However, using the search mode feature on the XLOOKUP, we can use the search mode code [-1] to tell the formula to search the list from the bottom up, which means the formula’s first match will pull David’s commission for 2023. 

Conclusion

To recap, the key differences between an XLOOKUP and a VLOOKUP are:

1. XLOOKUP can look for values to the left and right of the lookup array, while VLOOKUP is limited to only looking for values to the right of the lookup value column.
2. XLOOKUP allows you to customize text when a valid match is not found, while VLOOKUP only shows you an #N/A (error sign).
3. XLOOKUP allows you to specify a search mode (such as starting to look from the top or the bottom of a table) while VLOOKUP can only start looking for values from the top.

Overall, XLOOKUP is a far superior option to VLOOKUP as it has much greater flexibility. However, one notable downside to XLOOKUP is that it is not available in Excel 2016 and Excel 2019.

Additional Resources

If you want to learn more about Excel, check out our Excel for Business & Finance Course which has helped our students at Goldman Sachs, Bloomberg, Tesla, Amazon, and other top-tier companies.

Other Articles You Might Find Helpful

• Top 8 Most Important Excel Skills for Finance
• Build a Financial Dashboard
• Excel Data Validation
• Excel Filter
• Excel Sort Function