XLOOKUP

Search a range or array for a value and return the corresponding result from another range.

Lookup & Reference
|
Excel 365 only
|
Google Sheets Supported

Spreadsheet editor

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Returns: Varies

Arguments

Argument Required Description
lookup_value Yes The value to search for in the lookup_array.
lookup_array Yes The range or array to search for the lookup_value.
return_array Yes The range or array from which to return the result.
if_not_found No The value to return if no match is found. Defaults to #N/A if omitted.
match_mode No The type of match to perform. 0 for exact match (default), -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcard match.
search_mode No How to search the lookup_array. 1 searches first to last (default), -1 searches last to first, 2 uses binary search on ascending data, -2 uses binary search on descending data.

About

XLOOKUP searches a range or array for a value and returns the matching result from a separate return range. The three required arguments are the value to find, the range to search, and the range to return from. Unlike VLOOKUP, the return range can sit anywhere, including to the left of the search column, and works across both rows and columns.

Use XLOOKUP when you need to pull data from a table based on a match. The optional if_not_found argument replaces the default #N/A error with a custom message or value. The match_mode lets you switch between exact, approximate, and wildcard matching. The search_mode controls direction, so you can find the most recent entry in a list by searching from the last row backward.

XLOOKUP replaces most use cases for VLOOKUP and HLOOKUP, and handles many scenarios that previously required combining INDEX with MATCH. If you need the position of a match rather than the value, use XMATCH instead.

Examples

Handling missing matches with if_not_found

Change the name in B6 to something not in the list. Row 7 returns #N/A with no fallback. Row 8 uses the fourth argument to catch the missing match and show "Not found" instead.

Spreadsheet editor

Returning a value from a column to the left

Notice that Price sits to the left of the search column. Change the product code in B7 and watch XLOOKUP return the price. VLOOKUP can only look right. XLOOKUP works in any direction.

Spreadsheet editor

Finding the most recent entry with search_mode -1

Emma Wilson appears three times. Change the customer name in B8 and watch the result update to their last order. The -1 search mode makes XLOOKUP scan from the bottom up, returning the most recent match.

Spreadsheet editor

Watch out for

⚠

Lookup and return arrays must match in size

If lookup_array and return_array have a different number of rows (or columns for horizontal searches), XLOOKUP returns a #VALUE! error.

→ Make sure both arrays span the same number of cells. If your lookup range is A2:A100, your return range should also have 99 rows.

⚠

Missing if_not_found lets #N/A spread

Without a fourth argument, a missing match returns #N/A. Any formula that depends on that result also returns an error.

→ Add an if_not_found value as the fourth argument, such as "Not found", 0, or an empty string, to keep your sheet clean when a lookup fails.

⚠

Binary search on unsorted data gives wrong results

Using search_mode 2 or -2 (binary search) on unsorted data returns incorrect results without any error message.

→ Only use binary search modes when your lookup_array is sorted in the matching direction. For unsorted data, use the default search_mode 1.

⚠

Confusing match_mode with search_mode

match_mode sets how Excel decides if something is a match (exact, approximate, or wildcard). search_mode sets the direction Excel searches. Using the wrong parameter gives unexpected results.

→ Use match_mode when you want approximate or wildcard matching. Use search_mode when you need to search from last to first or use a binary search on sorted data.

Tips & notes

XLOOKUP is available in Excel for Microsoft 365, Excel 2021, and Excel 2024. It is not available in Excel 2019 or earlier. For those versions, use INDEX with MATCH as an alternative.

Common questions

Can XLOOKUP search horizontally?

Yes. XLOOKUP works with both rows and columns. Point lookup_array and return_array at horizontal ranges and it searches across them the same way it does vertically.

What is the difference between XLOOKUP and VLOOKUP?

XLOOKUP is more flexible. It can return a column to the left of the search column, handles missing values with the if_not_found argument, and can return multiple columns at once. VLOOKUP only searches downward and requires the return column to be to the right.

How do I return multiple columns with XLOOKUP?

Set return_array to a multi-column range. For example, =XLOOKUP(A2, D:D, E:G) returns values from three columns at once and spills them into adjacent cells automatically.

What does the search_mode argument do?

search_mode controls how Excel moves through the lookup_array. The default (1) searches from first to last. Use -1 to search from last to first, which is useful for finding the most recent entry. Use 2 or -2 for a binary search on sorted data, which performs better on large ranges.