For decades, VLOOKUP was the default lookup formula in spreadsheet applications. However, VLOOKUP came with notorious limitations: it could only search from left to right, broke whenever columns were inserted, required column index numbers, and struggled with multi-condition queries. In modern Google Sheets, XLOOKUP serves as the modern replacement, offering bidirectional searching, native error handling, and flexible match modes. While basic lookups are simple, mastering XLOOKUP’s advanced capabilities—such as searching across multiple criteria simultaneously and leveraging wildcard characters—unlocks powerful data querying workflows.
Understanding the Complete XLOOKUP Syntax
The full syntax of XLOOKUP in Google Sheets consists of six arguments (three required and three optional):
XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])The optional arguments provide significant control:
[missing_value]: Custom fallback returned when no match is found (e.g."Not Found"), eliminating the need forIFERROR().[match_mode]:0: Exact match (default).-1: Exact match or next smaller item.1: Exact match or next larger item.2: Wildcard match (supports*,?, and~).
[search_mode]:1: Search first-to-last (default).-1: Reverse search last-to-first (ideal for fetching most recent transactions).
1. Performing XLOOKUP with Multiple Criteria (Boolean Multiplication)
Traditional lookups require creating temporary helper columns to concatenate fields. With XLOOKUP, you can evaluate multiple independent conditions simultaneously using Boolean array multiplication.
The Formula Structure
=XLOOKUP(1, (Criteria_Range_1 = Condition_1) * (Criteria_Range_2 = Condition_2), Result_Range)Practical Example
Suppose you have sales data in columns A:C:
- Column
A: Department (e.g. Hardware, Software) - Column
B: Region (e.g. North, South) - Column
C: Revenue (e.g. 50,000, 85,000)
To find the Revenue for Hardware in the North region, enter:
=XLOOKUP(1, (A2:A20 = "Hardware") * (B2:B20 = "North"), C2:C20, "No Match Found")How It Works: Each comparison generates an array of TRUE (1) and FALSE (0) values. Multiplying the arrays produces 1 only where both conditions are satisfied simultaneously. XLOOKUP searches for 1 and returns the matching Revenue value.
2. Executing Partial and Wildcard Searches
When searching for customer names, product serials, or addresses where exact spelling is uncertain, enable wildcard mode by setting match_mode to 2.
Google Sheets supports three wildcard operators:
*(Asterisk): Represents any sequence of zero or more characters.?(Question Mark): Represents any single character.~(Tilde): Escapes a literal asterisk or question mark.
Example: Finding a Product by Partial SKU
Suppose product SKUs reside in column A (e.g. SKU-APP-1024-X) and prices in column B. To look up the price of any item containing 1024:
=XLOOKUP("*1024*", A2:A50, B2:B50, "Item Missing", 2)Example: Dynamic Search Based on User Input Cell
If a user types a partial search string into cell E1 (e.g. Smith), wrap the cell reference in asterisks:
=XLOOKUP("*" & E1 & "*", A2:A50, B2:B50, "Not Found", 2)3. Reverse Searching: Finding the Most Recent Entry
In transaction registers where new entries are logged at the bottom of the worksheet, standard lookups return the earliest record. Set search_mode to -1 to perform a bottom-to-top reverse search:
=XLOOKUP("Customer ABC", A2:A100, C2:C100, "No Orders", 0, -1)This instantly returns the customer’s most recent order without sorting the table.
Key Advantages Summary
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Directionality | Left-to-Right only | Bidirectional (Left, Right, Up, Down) |
| Multiple Criteria | Requires helper columns | Native Boolean array multiplication |
| Default Match Mode | Approximate (risk of false matches) | Exact match by default |
| Search Direction | Top-to-Bottom only | Top-to-Bottom or Bottom-to-Top |