XLOOKUP Function in Google Sheets

XLOOKUP in Google Sheets searches a column (or row) for a value and returns a result from the same position in a different column (or row). It was added to Sheets as a modern replacement for VLOOKUP and HLOOKUP, removing several of their limitations. XLOOKUP can search in any direction, handles missing values natively, and does not require your search column to be on the left.

Parameters

Parameter	Required	Description
search_key	Yes	The value to search for in lookup_range.
lookup_range	Yes	The single row or column to search. Must be the same size as result_range.
result_range	Yes	The single row or column from which to return a value. Must be the same size as lookup_range.
missing_value	No	The value to return if no match is found. If omitted, XLOOKUP returns #N/A on a failed match.
match_mode	No	0 = exact match (default). 1 = exact or next larger value. -1 = exact or next smaller value. 2 = wildcard match (supports * and ?).
search_mode	No	1 = search first to last (default). -1 = search last to first. 2 = binary search on ascending data. -2 = binary search on descending data.

Examples

Basic exact match

Look up a product ID in column A and return the product name from column B:

=XLOOKUP("SKU-205", A2:A100, B2:B100)

This searches A2:A100 for “SKU-205” and returns the corresponding value from B2:B100.

Custom missing value

Return “Not found” instead of #N/A when the lookup fails:

=XLOOKUP("SKU-999", A2:A100, B2:B100, "Not found")

If “SKU-999” does not exist in column A, the formula returns the text “Not found” instead of an error.

Reverse search (last to first)

Find the most recent entry for a customer when duplicates exist:

=XLOOKUP("Acme Corp", A2:A500, D2:D500, , 0, -1)

Setting search_mode to -1 searches from the bottom of the range upward, returning the last matching row. The two consecutive commas keep the missing_value as the default #N/A.

Common Errors

#N/A --- No match was found and no missing_value was provided. Verify the search key exists in the lookup range, and check for spacing or type mismatches.

Mismatched range sizes --- lookup_range and result_range must have the same number of cells. If A2:A100 has 99 cells but B2:B50 has 49, the formula breaks. Make sure both ranges have equal dimensions.

#VALUE! --- An invalid match_mode or search_mode was used. These arguments only accept the specific numeric values listed above.

Tips

Use the missing_value parameter to eliminate wrapper formulas. Where VLOOKUP requires =IFERROR(VLOOKUP(...), "Not found"), XLOOKUP handles it directly as the fourth argument.

XLOOKUP can return results from a column to the left of the search column. This was one of VLOOKUP’s biggest limitations and the primary reason people used INDEX MATCH instead.