XLOOKUP is better than VLOOKUP in any scenario. It's easier, faster, and more dynamic. Yet VLOOKUP is still more widely used than XLOOKUP, possibly due to a lack of awareness. In this post, we compare XLOOKUP vs VLOOKUP and show why XLOOKUP is a better replacement for VLOOKUP and HLOOKUP. If you want to get the most out of XLOOKUP, try Row Zero. Row Zero is a next-gen spreadsheet built for big data that is 1000x more powerful than traditional spreadsheets and lets you easily do XLOOKUPs across big datasets.
- What is XLOOKUP?
- 10 ways XLOOKUP is better than VLOOKUP
- Differences between XLOOKUP vs VLOOKUP
- Why do people still use VLOOKUP?
- Limitations of XLOOKUP
- Conclusion
What is XLOOKUP?
XLOOKUP is a spreadsheet function that searches for a value in a lookup range and returns a corresponding value from a return range. It was designed to replace VLOOKUP and is easier, more flexible, and more powerful than VLOOKUP. The XLOOKUP function uses the following syntax:
=XLOOKUP(lookup_value, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])
Here's a simple XLOOKUP example: 
The first 3 arguments are required:
- lookup_value is the key value you want to find.
- lookup_range is the range of cells where you want to perform the lookup.
- return_range is the range of cells containing the corresponding values you want to retrieve.
The next 3 arguments are optional:
- if_not_found [optional] specifies the value to return if no match is found.
- match_mode [optional] specifies how to match the lookup value:
- 0 - Exact match. If none found, return #N/A. This is the default.
- 1 - Exact match. If none found, return the next larger item.
- -1 - Exact match. If none found, return the next smaller item.
- 2 - Wildcard match. Allows searching with wildcards like *, ?, and ~
- search_mode [optional] specifies the search mode to use:
- 1 - Perform a search starting at the first item. This is the default.
- -1 - Perform a reverse search starting at the last item.
Here's a deeper look at how to use XLOOKUP and here's a spreadsheet with live XLOOKUP examples you can look at.
10 Ways XLOOKUP is better than VLOOKUP
XLOOKUP was designed to replace VLOOKUP and HLOOKUP and is better for any scenario. Here's a breakdown of the many ways XLOOKUP is better than VLOOKUP:
XLOOKUP is simpler and easier than VLOOKUP. The syntax is easier to understand, type, and execute.
XLOOKUP is faster than VLOOKUP, especially with big data and exact matches.
XLOOKUP is dynamic and more flexible:
- Column order doesn't matter (unlike VLOOKUP that requires the lookup range to be in the first column)
- XLOOKUP automatically adjusts if columns are added, deleted, or moved
- XLOOKUP has advanced arguments like match_mode and search_mode to more flexibly handle bigger and messier datasets and practical applications
XLOOKUP has built-in error handling with the if_not_found argument. Here's an example:
XLOOKUP can search both vertically and horizontally (columns and rows), so XLOOKUP replaces VLOOKUP and HLOOKUP.
XLOOKUP can search left-to-right or right-to-left (VLOOKUP only searches left-to-right)
XLOOKUP can search first to last or last to first (VLOOKUP only searches first to last) when there are duplicate values in the lookup range.
XLOOKUP handles approximate matching better. Unlike VLOOKUP, sorting is not required and you can configure using match_mode to look for exact match or next smaller (-1), exact match or next larger (1), or wildcard matching (2). Here are XLOOKUP approximate match examples:
Example of XLOOKUP Approximate Match -1 (Next Smaller):
If an exact match isn't found, XLOOKUP finds the next smaller value and returns the corresponding value from the return range.
Example of XLOOKUP Approximate Match 1 (Next Larger):
If an exact match isn't found, XLOOKUP finds the next larger value and returns the corresponding value from the return range.
XLOOKUP can easily handle multiple criteria using expressions. Here's an example:
The syntax for XLOOKUP with multiple criteria is:
XLOOKUP(1, (criteria1_range = criteria1_value) * (criteria2_range = criteria2_value), return_range)XLOOKUP works with data tables, so you can XLOOKUP pivot tables and connected tables in Row Zero to dynamically connect changing datasets with XLOOKUP.
As mentioned above, XLOOKUP can search rows horizontally so it is also an HLOOKUP replacement. XLOOKUP is better than HLOOKUP for the same reasons above.
Compare XLOOKUP vs VLOOKUP
Below we outline the differences between XLOOKUP and VLOOKUP
Lookup Direction Flexibility
- XLOOKUP: searches both vertically and horizontally and can search first to last or last to first
- VLOOKUP: search only vertically (down a column) and find the first result
Lookup Range Flexibility
- XLOOKUP: can search left-to-right or right-to-left.
- VLOOKUP: searches only left-to-right.
Dynamic Columns
- XLOOKUP: automatically adjusts if columns are added, deleted, or moved.
- VLOOKUP: column index must be manually updated if the table changes.
Range Selection
- XLOOKUP: uses separate arguments for lookup range and return range.
- VLOOKUP: requires selecting the entire table, including lookup and return columns.
Exact vs Approximate Match
- XLOOKUP: default is exact match; approximate match is optional
- VLOOKUP: default is approximate match; exact match requires FALSE.
Approximate Match
- XLOOKUP: No sorting required. Can be configured to find the next smaller (-1) or next larger (1) value.
- VLOOKUP: Must be sorted in ascending order for it to work correctly. Finds the largest value ≤ lookup_value.
Error Handling
- XLOOKUP: has built-in error handling with the if_not_found argument.
- VLOOKUP: requires IFERROR() or IFNA() for error handling.
Multiple Criteria
- XLOOKUP: can easily handle multiple criteria using expressions.
- VLOOKUP: difficult to use multiple criteria without helper columns.
Dynamic data and data tables
- XLOOKUP: can easily lookup data across dynamic data tables and pivot tables
- VLOOKUP: limited or no ability to lookup across dynamic data tables
Wildcard Support
- XLOOKUP: better wildcard support for partial matches
- VLOOKUP: limited wildcard support
Why do people still use VLOOKUP?
XLOOKUP is better than VLOOKUP in any scenario, yet VLOOKUP is still more widely used than XLOOKUP, possibly due to a lack of awareness and/or comfort level with VLOOKUP. Looking at Google Trends for XLOOKUP and VLOOKUP, VLOOKUP continues to have more interest than XLOOKUP by a wide margin, but XLOOKUP is gaining in popularity and closing the gap. 
When was XLOOKUP released?
XLOOKUP was first available in Excel in 2019 and Google Sheets in 2022. In 2024, Row Zero launched as a much more powerful spreadsheet and alternative to Excel and Google Sheets that can handle much larger XLOOKUPs. There is no reason to continue using VLOOKUP or HLOOKUP, yet millions of users still use these outdated functions.
Limitations of XLOOKUP
While XLOOKUP is better than VLOOKUP and HLOOKUP, it still has its limitations. Here are common XLOOKUP limitations:
XLOOKUP Slow in Excel and Google Sheets
Heavy use of XLOOKUP in Excel and Google Sheets can cause your spreadsheet to slow down or crash. XLOOKUP is a memory intensive function that searches every row in a dataset looking for a match, so as your dataset nears the limits of Excel and Google Sheets, your spreadsheet can slow down or freeze. If XLOOKUP is slow in Excel or crashing Google Sheets, try using Row Zero, a powerful spreadsheet designed for big data that lets you run XLOOKUPs on large data sets.
Handling Multiple Matches
XLOOKUP only returns the first match it finds. If you have multiple matching values and want to retrieve all of them, you need additional functions like FILTER or apply filters to your dataset.
Data Type Matching
The data type of the lookup value must match the type in the lookup range. For example, looking up a number formatted as text against actual numeric values will fail unless the formats are aligned.
Alternatives to XLOOKUP and VLOOKUP
There are a few alternatives to XLOOKUP that can work better in certain situations. As mentioned above, using the FILTER function or simply using the filter feature in your spreadsheet can be used when you need to see many matching results. Combining the functions INDEX and MATCH is sometimes used as an alternative to XLOOKUP and VLOOKUP. Depending on your dataset, pivot tables can also be used instead of XLOOKUP.
Conclusion
XLOOKUP is better, easier, and more powerful than VLOOKUP. If you're an XLOOKUP beginner, take a look at our XLOOKUP examples and XLOOKUP tutorial. If your VLOOKUP or XLOOKUP in Excel is slow, or your Google Sheets XLOOKUP is freezing, try using Row Zero. Row Zero is a next-gen spreadsheet built for big data that makes it easy to use XLOOKUP formulas to clean, join, or analyze large datasets.