XLOOKUP function

XLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.

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

Generate a XLOOKUP formula

Describe what you need. The generator will reach for XLOOKUP where XLOOKUP is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How XLOOKUP reads its arguments
lookup_valuerequiredlookup_arrayrequiredreturn_arrayrequiredif_not_foundoptionalmatch_modeoptionalsearch_modeoptionalXLOOKUP →
ArgumentRequiredDescription
lookup_valueRequiredThe value to search for; can be a number, text, or reference, and XLOOKUP will compare it against each element of lookup_array.
lookup_arrayRequiredA one-dimensional range or array where XLOOKUP searches for lookup_value; if the range is empty or mismatched, an error occurs.
return_arrayRequiredA one-dimensional range or array of the same size as lookup_array whose corresponding element is returned; mismatched sizes raise #VALUE!.
if_not_foundOptionalOptional text, number, or formula returned when lookup_value is not found; omitted causes a #N/A error.
match_modeOptionalOptional integer 0 (exact, default), -1 (exact or next smaller), 1 (exact or next larger), or 2 (wildcard) that controls how matches are evaluated.
search_modeOptionalOptional integer 1 (search first to last, default) or -1 (search last to first) that determines the direction XLOOKUP scans the lookup_array.

Returns

XLOOKUP returns a single value or an array of values matching the shape of the return_array.

Availability

Excel: 365 / 2021+ · Google Sheets: Supported

Worked examples

1. Find the cost of a specific SKU

SKUWarehouseOn HandReorder PointCost
A100North1205015
C300East0209.75
=XLOOKUP("C300", Inventory!A2:A6, Inventory!E2:E6, "SKU not found")

Result: 9.75

XLOOKUP looks for "C300" in the SKU column (A2:A6). When it finds the match on the third row, it returns the corresponding entry from the Cost column (E2:E6), which is 9.75. Because we supplied an if_not_found string, the formula would show "SKU not found" only if the SKU were missing.

2. Identify the warehouse for out-of-stock items

SKUWarehouseOn HandReorder PointCost
B200South304022.5
C300East0209.75
=XLOOKUP(0, Inventory!C2:C6, Inventory!B2:B6, "None", 0, 1)

Result: East

The lookup_value 0 is compared against the On Hand column (C2:C6). The first exact match occurs on the row containing SKU C300. XLOOKUP then returns the Warehouse entry from column B (B2:B6) for that same row, which is "East". The optional if_not_found argument ensures a friendly message if no zero quantity exists.

3. Get the reorder point for the most expensive product

SKUWarehouseOn HandReorder PointCost
A100North1205015
B200South304022.5
E500North2001505
=XLOOKUP(MAX(Inventory!E2:E6), Inventory!E2:E6, Inventory!D2:D6, "N/A")

Result: 40

MAX(Inventory!E2:E6) evaluates to 22.50, the highest cost in the Cost column. XLOOKUP then searches that value within the same Cost column and, upon finding it on the row for SKU B200, returns the matching Reorder Point from column D (D2:D6). The result is 40, the reorder point for the most expensive SKU.

Common errors

Which XLOOKUP error are you seeing?
XLOOKUP returned an error#N/A
Add a fourth argument, such as "Not found", to supply a default result when the value is missing.
#VALUE!
Resize the return_array so it matches the exact row count of lookup_array, or use a dynamic range like OFFSET to align them.
#SPILL!
Move the formula to a location with enough empty cells to accommodate the returned array, or limit the return_array to a single column.
ErrorWhy it happensHow to fix it
#N/AThe lookup_value does not exist in lookup_array and no if_not_found argument was supplied.Add a fourth argument, such as "Not found", to supply a default result when the value is missing.
#VALUE!lookup_array and return_array have different dimensions (e.g., one has five rows while the other has six).Resize the return_array so it matches the exact row count of lookup_array, or use a dynamic range like OFFSET to align them.
#SPILL!The formula is returning a multi-column array (e.g., =XLOOKUP("North", Inventory!B2:B6, Inventory!C2:E6)) but adjacent cells are occupied, preventing the spill.Move the formula to a location with enough empty cells to accommodate the returned array, or limit the return_array to a single column.

Tips and when to use something else

  • Use match_mode 0 for strict exact matches; avoid unexpected wildcard matches unless you deliberately need them.
  • Set search_mode to –1 when you want the last occurrence of a duplicate lookup_value, such as finding the most recent shipment.
  • Wrap XLOOKUP in IFERROR or IFNA to present user-friendly messages instead of raw error codes.
  • When you need a left-lookup (searching a column to return a value from a column to its left), consider VLOOKUP with the column index or the combination of INDEX and MATCH instead.

Related functions

VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.XMATCHXMATCH returns the relative position of a lookup value within a one-dimensional range, supporting exact, wildcard and next-larger/smaller matches.CHOOSECHOOSE returns the value or range from a list of arguments that corresponds to the supplied index number, letting you pick one of several options.

Frequently asked questions

How does XLOOKUP differ from VLOOKUP?
XLOOKUP can search in any direction, return values from columns to the left or right, and includes built-in error handling, whereas VLOOKUP is limited to left-to-right lookups and requires a separate IFERROR wrapper for missing values.
Can XLOOKUP search from bottom to top?
Yes. By setting the optional search_mode argument to –1, XLOOKUP scans the lookup_array in reverse order, returning the first match it encounters from the bottom of the range.
What does match_mode 2 do?
Match_mode 2 enables wildcard matching, allowing the lookup_value to contain * (any sequence) or ? (any single character). This is useful for partial SKU codes or pattern-based searches.
Why does XLOOKUP sometimes return #N/A even though the value exists?
If the lookup_array contains hidden characters, extra spaces, or different data types (e.g., number stored as text), XLOOKUP may treat them as mismatches. Clean the data with TRIM or VALUE, or use the exact match mode to avoid implicit conversions.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator