Master XLOOKUP: Simplify Data Search and Learn the Key Differences from VLOOKUP and HLOOKUP

2025年1月17日

Have you ever found yourself struggling to search for data in Google Sheets, especially when using traditional functions like VLOOKUP or HLOOKUP?

As your data grows and you keep adding new rows or columns, managing the search range becomes increasingly challenging. Adjusting these functions can be frustrating when columns or rows are modified, often causing errors or inefficiencies.

When your datasets become larger and more complex, it’s easy to feel restricted by the fixed lookup direction and reference limitations of these functions.

This is where XLOOKUP comes to the rescue.

XLOOKUP is the modern upgrade to traditional lookup functions like VLOOKUP and HLOOKUP. It provides a more flexible and powerful way to search for data, overcoming the limitations of its predecessors.

In this article, we’ll explore the basics of XLOOKUP and how it works. You’ll discover how it can simplify data searching, even as your dataset grows in size and complexity, making your workflow more efficient and stress-free.

TOC

Differences Between XLOOKUP, VLOOKUP, and HLOOKUP

Feature	XLOOKUP	VLOOKUP	HLOOKUP
Search Direction	Supports both vertical and horizontal	Vertical only	Horizontal only
Search Column/Row	Any column or row	Leftmost column only	Topmost row only
Multi-Directional Search	Possible	Not possible	Not possible
Flexibility in Range of Results	Can return any value	Fixed range only	Fixed range only
Return Value When Not Found	Customizable	Error	Error
Formula Complexity	Slightly complex	Relatively simple	Relatively simple
Error Handling	Custom return value possible	Handled with IFERROR	Handled with IFERROR
Use Case	Flexible data searches	Basic vertical searches	Basic horizontal searches
Performance	May slow down with large datasets	Fast with simple data	Fast with simple data

Notes

Features with high flexibility, such as search direction, search column/row, and multi-directional search, are marked as ◎.
Features that are adequate for most use cases or allow for basic searches are marked as 〇.
Features with limitations compared to other functions are marked as △.
Features with no flexibility or only useful in limited scenarios are marked as ✕.

XLOOKUP supports both vertical and horizontal directions.
Its strength lies in its flexibility to search data regardless of its arrangement.

XLOOKUP is a new function that allows you to search data in both vertical and horizontal directions.

Unlike traditional functions like VLOOKUP and HLOOKUP, which are limited by search direction, XLOOKUP offers the flexibility to search from any column or row.

If the search result is not found, XLOOKUP lets you display a custom message, such as “Not Found,” instead of an error message, making the results more user-friendly.

This feature simplifies handling situations where data is missing and enables more efficient error management.

Differences from VLOOKUP

VLOOKUP searches data vertically, but it always bases the search on the leftmost column and retrieves data from columns to the right.

As a result, if the search target is not in the leftmost column, you’ll need to restructure your sheet to make it work.

Differences from HLOOKUP

HLOOKUP searches data horizontally, starting from the top row and retrieving corresponding values.

This makes it unsuitable for cases where data is arranged vertically or when you need to search using multiple criteria.

XLOOKUP Syntax and Basic Usage

The basic syntax of the XLOOKUP function is as follows:

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

lookup_value: The value you want to search for.
lookup_array: The range or array where the function will search for the lookup_value.
return_array: The range or array from which the corresponding value will be returned.
[if_not_found](optional): The value to return if the lookup_value is not found (e.g., “Not Found”).
[match_mode](optional): Specifies the type of match to perform:
　　0 (default): Exact match.
　　-1: Exact match or the next smaller item.
　　1: Exact match or the next larger item.
　　2: Wildcard match.
[search_mode](optional): Determines the search direction:
　　1 (default): Search from first to last.
　　-1: Search from last to first.
　　2: Perform a binary search in ascending order.
　　-2: Perform a binary search in descending order.

Match Mode

The match mode parameter allows you to specify whether the function performs an exact or approximate match:

0: Performs an exact match.
1 or -1: Performs an approximate match.

For example, by using 1 or -1, you can retrieve the closest value to the lookup value, even if an exact match is not found. This feature is particularly useful when dealing with ranges or approximate data.

Search Mode

The search mode parameter specifies the order in which the search is performed:

1: Searches sequentially from the first value to the last.
-1: Searches in reverse order, starting from the last value and moving to the first.

This option allows you to control the order of the data retrieved when there are multiple occurrences of the same value.

Basic Usage Example ①

In traditional VLOOKUP, you could only search from left to right. However, with XLOOKUP, you can search in any direction, starting from any column.

For example, if you want to retrieve a product ID by entering a product name, here’s how you can do it:

E2: Product Name (e.g., “Notebook”)
F2: Product ID (Search Result)

To fetch the corresponding product ID for the product name “Notebook,” you can use the following XLOOKUP formula:

=XLOOKUP(E2, B2:B, A2:A, "Product not found")

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

E2: The value to search for (in this case, “Notebook”).
B2:B: The range containing product names.
A2:A: The range containing product IDs to return.
"Product not found": The message displayed if the search value is not found.

Using this formula, you can fetch the product ID “102” corresponding to the product name “Notebook”.

This demonstrates the flexibility of XLOOKUP, allowing you to perform reverse lookups (from right to left), which is not possible with traditional VLOOKUP.

Basic Usage Example ② (Search Data from Bottom to Top)

If you need to search data starting from the bottom, XLOOKUP provides a convenient way by setting the search mode to -1. This allows the search to proceed in reverse order, from the last entry to the first.

For example, suppose you have the following table:

Column A: Meeting Date
Column B: Participant Name
Column C: Meeting Details

You want to retrieve the most recent meeting date for a specific participant.

E2: The participant’s name to search for (e.g., “Tanaka”).
F2: The most recent meeting date.

XLOOKUP Formula:

=XLOOKUP(E2, B2:B, A2:A, "No meeting found", 0, -1)

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

Formula Breakdown:

E2: The value to search for (participant’s name, e.g., “Tanaka”).
B2:B: The range containing participant names.
A2:A: The range containing meeting dates to return.
"No meeting found": The custom message displayed if no match is found.
0: Exact match for the participant’s name.
-1: Reverse search mode, searching from the bottom up.

Using this formula, you can retrieve the most recent meeting date for “Tanaka”, making it easy to locate the latest records in your data.

If you want to find the details of the most recent meeting, you can use the following formula:

=XLOOKUP(E2, B2:B, C2:C, "No meeting found", 0, -1)

Formula Breakdown:

E2: The participant’s name to search for (e.g., “Tanaka”).
B2:B: The range containing participant names.
C2:C: The range containing meeting details to return.
"No meeting found": Custom message if no match is found.
0: Exact match for the participant’s name.
-1: Reverse search mode, searching from the bottom up.

By using XLOOKUP in this way, you can easily retrieve the most recent meeting details by searching from bottom to top, making it an efficient tool for handling time-sensitive data.

When to Use XLOOKUP and How to Differentiate It from Other Functions

When to Use XLOOKUP

For Flexible Searches: Use XLOOKUP when you need to search data flexibly, especially if your dataset is arranged both vertically and horizontally, or if you need to search in both right and left directions.
To Reduce Errors: If you want to avoid returning confusing error messages when a lookup value isn’t found, XLOOKUP allows you to display a user-friendly message instead, such as “No data found.”
For Multi-Condition Searches: When you need to search using multiple conditions, XLOOKUP can handle these complex queries efficiently, making it ideal for advanced scenarios.

When to Use VLOOKUP

For Simple Vertical Searches: VLOOKUP is ideal when you only need to search vertically from the leftmost column to retrieve data from the columns to its right.
When Data is Well-Organized: If your dataset is neatly arranged and searching from a specific column is sufficient, VLOOKUP provides a straightforward solution.

When to Use HLOOKUP

For Simple Horizontal Searches: HLOOKUP is useful when you need to retrieve values from data arranged horizontally, starting from the top row.
When Data is Arranged Horizontally: If your dataset is organized in rows and you want to search from the first row, HLOOKUP provides a convenient and straightforward solution.

Conclusion

XLOOKUP addresses the limitations of traditional functions like VLOOKUP and HLOOKUP, offering a more flexible and efficient way to search for data.

Key advantages include:

The ability to search in both vertical and horizontal directions.
Simple and customizable error handling.

XLOOKUP becomes especially valuable as your datasets grow and become more complex. Give it a try and experience the difference!

Our company offers support for improving work efficiency through the use of Google Apps Script.
If you need assistance with Google Apps Script customization or error resolution, please feel free to contact us.
We are fully committed to supporting your business improvements.

Contact us here