XLOOKUP vs VLOOKUP Key Differences

What’s the difference between XLOOKUP and VLOOKUP in Excel?

3 minute read
Excel Spreadsheet Lookup Formulas


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.


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).


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).

Example Comparison - flexible lookup array

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.

Example Comparison - match not found

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).

Example Comparison - Search Mode

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. 


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


Building a cash flow statement from scratch using a company income statement and balance sheet is one of the most fundamental finance exercises commonly used to test interns and full-time professionals at elite level finance firms.

Test hyperlink

Image caption goes here
Sample Image Insertion
Dolor enim eu tortor urna sed duis nulla. Aliquam vestibulum, nulla odio nisl vitae. In aliquet pellentesque aenean hac vestibulum turpis mi bibendum diam. Tempor integer aliquam in vitae malesuada fringilla.

Elit nisi in eleifend sed nisi. Pulvinar at orci, proin imperdiet commodo consectetur convallis risus. Sed condimentum enim dignissim adipiscing faucibus consequat, urna. Viverra purus et erat auctor aliquam. Risus, volutpat vulputate posuere purus sit congue convallis aliquet. Arcu id augue ut feugiat donec porttitor neque. Mauris, neque ultricies eu vestibulum, bibendum quam lorem id. Dolor lacus, eget nunc lectus in tellus, pharetra, porttitor.

  • Test Bullet List 1
  • Test Bullet List 2
  • Test Bullet List 3
"Ipsum sit mattis nulla quam nulla. Gravida id gravida ac enim mauris id. Non pellentesque congue eget consectetur turpis. Sapien, dictum molestie sem tempor. Diam elit, orci, tincidunt aenean tempus."

Tristique odio senectus nam posuere ornare leo metus, ultricies. Blandit duis ultricies vulputate morbi feugiat cras placerat elit. Aliquam tellus lorem sed ac. Montes, sed mattis pellentesque suscipit accumsan. Cursus viverra aenean magna risus elementum faucibus molestie pellentesque. Arcu ultricies sed mauris vestibulum.


Morbi sed imperdiet in ipsum, adipiscing elit dui lectus. Tellus id scelerisque est ultricies ultricies. Duis est sit sed leo nisl, blandit elit sagittis. Quisque tristique consequat quam sed. Nisl at scelerisque amet nulla purus habitasse.

Nunc sed faucibus bibendum feugiat sed interdum. Ipsum egestas condimentum mi massa. In tincidunt pharetra consectetur sed duis facilisis metus. Etiam egestas in nec sed et. Quis lobortis at sit dictum eget nibh tortor commodo cursus.

Odio felis sagittis, morbi feugiat tortor vitae feugiat fusce aliquet. Nam elementum urna nisi aliquet erat dolor enim. Ornare id morbi eget ipsum. Aliquam senectus neque ut id eget consectetur dictum. Donec posuere pharetra odio consequat scelerisque et, nunc tortor.
Nulla adipiscing erat a erat. Condimentum lorem posuere gravida enim posuere cursus diam.

Kenji Farre
Kenji Farre
Senior Instructor

Ready to Level Up Your Career?

Learn the practical skills used at Fortune 500 companies across the globe.