The XLOOKUP function in Google Sheets is a modern and flexible lookup function that simplifies searching for data in both vertical and horizontal ranges. Unlike VLOOKUP or HLOOKUP, XLOOKUP can search in any direction and handle missing values more efficiently.

1. What is XLOOKUP?

XLOOKUP searches for a specific value in a range and returns a corresponding value from another range.

It combines the functionality of VLOOKUP, HLOOKUP, and INDEX-MATCH into a single, easy-to-use function.

Syntax:
=XLOOKUP(search_key, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])

• search_key — The value to search for
• lookup_range — The range to search in
• return_range — The range from which to return a value
• if_not_found — (Optional) Value to return if not found
• match_mode — (Optional) 0 for exact match (default), -1 for exact or next smaller, 1 for exact or next larger
• search_mode — (Optional) 1 for search first to last (default), -1 for search last to first

2. Simple Example

=XLOOKUP(101, A2:A10, B2:B10, "Not Found")

• Searches for 101 in A2:A10
• Returns the corresponding value from B2:B10
• If 101 is not found, it returns “Not Found”

3. Advantages of XLOOKUP

Searches both vertically and horizontally
No need to specify column index like VLOOKUP
Handles missing values gracefully
Supports approximate matches and reverse searches
Simpler syntax than combining INDEX and MATCH

4. Use Cases

Retrieve employee names based on ID
Find product prices using product codes
Match sales records to customer IDs
Handle dynamic datasets with flexible lookup requirements

5. Tips for Using XLOOKUP

Always use the if_not_found argument to avoid errors
Ensure lookup and return ranges are the same size
Use match_mode for approximate matches when needed
Combine with other functions like IF or ARRAYFORMULA for advanced solutions

Conclusion

XLOOKUP in Google Sheets is a powerful, flexible lookup function that replaces traditional VLOOKUP, HLOOKUP, and INDEX-MATCH in most scenarios.

Mastering XLOOKUP allows you to perform faster, more accurate lookups, handle missing data, and simplify complex spreadsheet tasks.