The XLOOKUP function is a powerful tool that allows users to search for a value in a lookup range and return a corresponding value from a return range. This function is particularly useful when working with large datasets and is better than VLOOKUP and HLOOKUP. Row Zero is a next-gen spreadsheet built for big data that makes it easy to use XLOOKUP across large datasets in a spreadsheet. Continue reading to learn how to use XLOOKUP and view XLOOKUP examples.
- What is XLOOKUP?
- How to use XLOOKUP
- XLOOKUP Examples
- XLOOKUP vs VLOOKUP and HLOOKUP
- Troubleshooting common XLOOKUP issues
- When to use the XLOOKUP function
- Conclusion
What is XLOOKUP?
XLOOKUP is a spreadsheet function that looks up a specified value in a range and returns a corresponding value from another range. 
XLOOKUP can search both vertically (columns) and horizontally (rows) and is a better replacement for both VLOOKUP and HLOOKUP. The XLOOKUP function is very versatile and can be used to merge CSV files, can dynamically lookup up changing datasets, and can lookup across pivot tables, data tables, and across sheets. The XLOOKUP function uses the following syntax:
=XLOOKUP(lookup_value, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])
The first 3 arugments 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.
Notes: Binary search is typically used to improve performance on large datasets. To make it work, your data must be sorted. Row Zero implements an advanced algorithm that allows the normal search modes to work quickly on unsorted data, so modes 1 and -1 should be used, unlike Excel, which offers additional options 2 (binary search, ascending order) and -2 (binary search, descending order). Row Zero does not yet support wildcard match_mode in XLOOKUP.
If you are a beginner to XLOOKUP, first try it out with just the first 3 arguments. You may not need to use the optional XLOOKUP arguments in many scenarios.
How to use XLOOKUP
Here is an XLOOKUP tutorial that shows how to use XLOOKUP and how XLOOKUP compares to VLOOKUP:
The first half of the video demonstrates a simple XLOOKUP example that merges data from multiple CSV files. Skip to 3:41 to see an example of a dynamic XLOOKUP across pivot tables.
XLOOKUP Examples
Below, you'll find sample XLOOKUP examples that demonstrate how to do an XLOOKUP - from simple XLOOKUPs to more advanced XLOOKUP examples. You can view these XLOOKUP examples live in a spreadsheet here.
Simple XLOOKUP examples
Here is a basic column XLOOKUP that you would use instead of VLOOKUP:
Here is a basic row XLOOKUP that you would use instead of HLOOKUP:
XLOOKUP if_not_found to handle missing values
Here is an example of XLOOKUP using the if_not_found parameter to handle missing values: 
XLOOKUP Match_Mode - How XLOOKUP approximate match works
The XLOOKUP match_mode parameter has the following options:
- 0 – Exact match. If not found, returns the value given for the if_not_found paramater. This is the default option
- -1 – Exact match or next smaller item
- 1 – Exact match or next larger item
- 2 – Wildcard match using *, ?, and ~
XLOOKUP match_mode examples with approximate match
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 Search_Mode Examples
When you may have multiple instances of a key value in the lookup range, you can use the search_mode argument to specify whether you want to lookup the first instance or the last instance. XLOOKUP search_mode has the following options:
- 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 an example XLOOKUP search mode searching first instance (this is default):
Here's an example XLOOKUP reverse search using search_mode -1 that searches last to first:
XLOOKUP with multiple criteria
Here's how to XLOOKUP multiple criteria using expressions: 
When you do an XLOOKUP with multiple criteria, it looks for the first instance where all criteria match and returns the corresponding value in the return range.
The syntax for XLOOKUP with multiple criteria is: XLOOKUP(1, (criteria1_range = criteria1_value) * (criteria2_range = criteria2_value), return_range)
Here's how it works:
- (criteria1_range = criteria1_value) creates a Boolean array (TRUE/FALSE).
- Multiplying multiple conditions (*) acts like an AND condition, converting TRUE/FALSE into 1/0.
- The lookup value is 1 because the combined condition will return 1 where all conditions are TRUE.
- return_range is the range of values to return when the criteria are met.
Note you can use more than two criteria since this formula is effectively mutliplying 1 and/or 0 for each criteria. However, using many XLOOKUP criteria may impact performance.
XLOOKUP vs VLOOKUP and HLOOKUP
Comparing XLOOKUP vs VLOOKUP, XLOOKUP is better than VLOOKUP in several ways:
- XLOOKUP is simpler and easier than VLOOKUP
- XLOOKUP is faster than VLOOKUP, especially with big data
- 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 works with data tables, so you can XLOOKUP pivot tables and connected tables to dynamically connect data with XLOOKUP.
- 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 has built-in error handling with the if_not_found argument.
- XLOOKUP can easily handle multiple criteria using expressions
- XLOOKUP has advanced arguments like match_mode and search_mode to more flexibly handle a bigger and messier datasets and practical applications
Similarly, XLOOKUP is better than HLOOKUP for the same reasons above.
Troubleshooting Common XLOOKUP Issues
While XLOOKUP is a powerful spreadsheet function and significantly better than VLOOKUP and HLOOKUP, it's not immune to challenges that may arise during usage. There could be a number of reasons why your XLOOKUP does not work. Below are common issues with XLOOKUP usage and tips to avoid and troubleshoot XLOOKUP problems.
XLOOKUP Slow in Excel and Google Sheets
XLOOKUP is a memory intensive function. When running an XLOOKUP in Excel or Google Sheets over large datasets, the spreadsheet can slow down or crash. The cause of poor performance with XLOOKUP is due to the function's design. XLOOKUP searches every row in the dataset looking for a match. As the data set grows, there are more pieces of data to look through, thus increasing the memory requirement. 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.
Common XLOOKUP Mistakes to Avoid
While XLOOKUP is easy to use, there are some common XLOOKUP errors to avoid:
- Mismatched Ranges: Ensure lookup_range and return_range are the same size length. For example, you would get an error in this formula =XLOOKUP("SFO",A1:A100, B1:B101) because the return range is larger than the lookup range.
- Duplicate values: By default, XLOOKUP only returns the first match it finds in the lookup range. If you set the parameter search_mode to -1, it returns the last match in the lookup range. If you have several duplicate values in your lookup range with different corresponding values in the return range, you may not get the data you want. In this scenario, it can be helpful to sort your return range so it can return something logical like the biggest, smallest, earliest, or latest.
- Neglecting Error Handling: Not using the optional if_not_found argument can lead to #N/A errors.
- Incorrect Match Modes or Search Mode: Understand the difference between exact match and approximate match and regular search vs reverse search.
- Heavy usage in a traditional spreadsheet - If your spreadsheet has many XLOOKUPs or a big XLOOKUP in Excel, it can cause performance issues. Same for XLOOKUP in Google Sheets. If your XLOOKUP is slow, try using a more powerful spreadsheet like Row Zero.
- Inconsistent data types or formatting - Confirm the data types and formats between the key (lookup value) and lookup_range are compatible. For example, if you have "3" as text as your lookup value but it's 3 as a number in your lookup range, the XLOOKUP may fail.
- Incorrect reference ranges - While the flexibility of using any column or row as the lookup_range or return_range is great, it's a simple mistake to select the wrong range and not notice because the XLOOKUP may still return "normal" looking results.
- Forgetting to lock cell references when dragging formulas - Just like any spreadsheet formula, dragging formulas without locking cell references with "$" can cause errors
XLOOKUP Tips for Maximizing Efficiency
To help you make the most of the XLOOKUP function, here are some XLOOKUP tips and tricks to maximize your efficiency and productivity.
Clean up your data first
Cleaning up your dataset before running XLOOKUP formulas can help you minimize XLOOKUP errors and ensure accurate results. Some data cleaning techniques to consider are removing duplicates and fixing formatting inconsistencies - ensure that dates are in the correct date format, numbers are formatted as numbers, and text is standardized.
Use XLOOKUP eror handling
Use the if_not_found parameter to prevent disruptive errors in your reports. One simple solution is to enter double quotes (e.g. "") so that it returns an empty cell if nothing is found.
Use dynamic named ranges
It can be helpful to assign meaningful names to your data ranges. By doing so, you can easily refer to specific ranges in your XLOOKUP formulas, making them more understandable and maintainable.
When to Use the XLOOKUP Function
- Replace VLOOKUP with XLOOKUP for more flexibility and efficiency, especially if you have a slow VLOOKUP or are getting VLOOKUP errors.
- Replace HLOOKUP with XLOOKUP for the same reasons.
- Dynamic data: when working with changing datasets that require adaptable lookups. In Row Zero, XLOOKUP works with pivot tables and connected tables and auto-updates with new data or changes.
- Bidirectional Lookups: XLOOKUP can search data in all directions: vertically or horizontally, left or right, and up or down.
- Error management - XLOOKUP let's you customize the error handling with if_not_found and eliminate #N/A errors.
- Searching for values in reverse - XLOOKUP lets you search for a value in a lookup range both top to bottom and bottom to top.
Conclusion
The XLOOKUP function is a powerful tool for searching and retrieving specific information across multiple columns, rows, or sheets. You can also use XLOOKUP to join CSV files or multiple datasets in a spreadsheet. XLOOKUP is better, easier, and more powerful than VLOOKUP. While XLOOKUP may look daunting at first, the concept is simple and easy to use. If you're an XLOOKUP beginner, step through the XLOOKUP examples above and you'll soon feel comfortable with simple XLOOKUPs and be able to move on to more advanced XLOOKUP formulas. If your XLOOKUP in Excel is slow, or your Google Sheets XLOOKUP is freezing, try using Row Zero. Row Zero is a next-gen spreadsheet designed for big data that makes it easy to use XLOOKUP formulas to clean, join, or analyze large datasets.