How to Use the XLOOKUP Function in Google Sheets for Bidirectional and Multi-Condition Searches

For decades, spreadsheet users relied on VLOOKUP and INDEX/MATCH combinations to retrieve data from reference tables. While VLOOKUP remains widely recognized, it suffers from severe functional constraints: it can only search from left to right, breaks whenever columns are inserted or reordered, and defaults to dangerous approximate matching unless an exact match flag is explicitly passed. In Google Sheets, the modern XLOOKUP function replaces VLOOKUP, HLOOKUP, and INDEX/MATCH with a unified, resilient formula structure that searches in any direction.

Syntax and Parameter Overview of XLOOKUP

The core syntax of XLOOKUP in Google Sheets is defined as follows:

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

The first three parameters are mandatory, while the remaining three provide robust control:

• search_key: The value you want to search for (e.g., an Employee ID or Product SKU).
• lookup_range: The single column or row containing values to match against.
• result_range: The corresponding column or row containing the data to return.
• [missing_value]: The fallback text or calculation returned if no match is found (eliminating nested IFERROR wrappers).
• [match_mode]: 0 for exact match (default), -1 for exact match or next smaller item, 1 for exact match or next larger item, 2 for wildcard matching.
• [search_mode]: 1 to search from first to last (default), -1 to search from last to first (finding the latest transaction), 2 or -2 for binary searches on sorted data.

Performing a Reverse (Left) Lookup with XLOOKUP

Unlike VLOOKUP, which requires the search key to reside in the leftmost column of the table, XLOOKUP handles lookups regardless of column positioning. Suppose employee names are in Column A and their Employee IDs are in Column B. To find an employee name based on an ID entered in cell D2:

=XLOOKUP(D2, B2:B100, A2:A100, "Employee Not Found")

Because the lookup range (B2:B100) and return range (A2:A100) are supplied independently, inserting new columns between A and B will never corrupt the formula calculation.

Executing Multi-Condition Lookups with the Ampersand (&) Operator

When searching for records requiring two or more matching criteria—such as finding the salary of an employee matching both a specific Department (Column A) and a specific Job Title (Column B)—you can chain search keys and ranges together:

=XLOOKUP(F2 & G2, A2:A100 & B2:B100, C2:C100, "No Match Found")

Here, cell F2 contains the Department, G2 holds the Job Title, and C2:C100 contains the salary. Google Sheets concatenates the arrays in memory and locates the exact matching record.

Returning Multiple Columns Simultaneously (Array Spilling)

In Google Sheets, XLOOKUP supports dynamic array output. If you want a single formula to retrieve an employee’s Department, Office Location, and Salary concurrently, specify a multi-column result range:

=XLOOKUP(E2, A2:A50, B2:D50, "Not Found")

When matching Employee ID in E2, the formula spills across three adjacent horizontal cells, populating Department (B), Location (C), and Salary (D) with zero formula dragging.

Comparison: VLOOKUP vs. XLOOKUP in Google Sheets

Capability	VLOOKUP	XLOOKUP
Lookup Direction	Left-to-right only	Any direction (Left, Right, Up, Down)
Default Match Mode	Approximate (Risk of false data)	Exact Match (Safe by default)
Missing Value Handling	Requires IFERROR() wrapping	Built-in [missing_value] parameter
Column Insertion Resilience	Breaks (Static column index)	Fully resilient (Dynamic range binding)