How to Use XLOOKUP in Google Sheets
XLOOKUP is a more powerful alternative to VLOOKUP. It can search in any direction, return entire arrays, and handle missing values gracefully.
Understand XLOOKUP Syntax
XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]).
Set Up a Basic XLOOKUP
Use =XLOOKUP(A2, D:D, E:E) to find the value in A2 within column D and return the matching value from column E.
💡 Unlike VLOOKUP, the lookup range does not have to be the first column.
Handle Missing Values
Add a fourth parameter for missing values: =XLOOKUP(A2, D:D, E:E, "Not found") displays "Not found" when there is no match.
Use Match Modes
Set match_mode to 0 for exact match, 1 for next smaller, -1 for next larger, or 2 for wildcard matching.
Try These Templates
Frequently Asked Questions
Is XLOOKUP available in all Google Sheets accounts?
Yes. XLOOKUP is available in all Google Sheets accounts.
How is XLOOKUP better than VLOOKUP?
XLOOKUP can search left or right, return entire rows or columns, handle errors, and use multiple match modes.