Excel XLOOKUP Function

1. 1

   lookup_value

   Required

   The value to find. It can be text, a number, a cell reference, or the result of another formula.

2. 2

   lookup_array

   Required

   The row or column where Excel should search for lookup_value.

3. 3

   return_array

   Required

   The row, column, or table of values to return from the matching position.

4. 4

   if_not_found

   Optional

   The fallback result to return when no match is found. If omitted, Excel returns #N/A.

5. 5

   match_mode

   Optional

   Controls exact, approximate, or wildcard matching:

   • 0 — Exact match: Returns a result only when XLOOKUP finds the lookup value exactly. This is the default.
   • -1 — Exact or next smaller: Returns an exact match, or the next smaller item when no exact match exists.
   • 1 — Exact or next larger: Returns an exact match, or the next larger item when no exact match exists.
   • 2 — Wildcard match: Allows wildcard characters such as asterisks and question marks in the lookup value.
6. 6

   search_mode

   Optional

   Controls the direction or method of the search:

   • 1 — First to last: Searches from the first item to the last item. This is the default.
   • -1 — Last to first: Searches from the last item to the first item.
   • 2 — Binary ascending: Uses binary search on data sorted in ascending order.
   • -2 — Binary descending: Uses binary search on data sorted in descending order.

• Array alignment matters

  lookup_array and return_array must line up by position. A match in the fifth lookup item returns the fifth item from the return array.

• Exact match is the default

  Unlike the older VLOOKUP and HLOOKUP functions, XLOOKUP looks for an exact match unless you choose a different match_mode.

• The fallback only handles missing matches

  if_not_found replaces #N/A when no match exists, but it does not fix unrelated errors in the lookup or return data.

• Binary search requires sorted data

  Only use search_mode values 2 or -2 when the lookup array is sorted in the matching direction.

Replacing older lookups? XLOOKUP can return values on either side of a vertical lookup or above and below a horizontal lookup, while the older VLOOKUP can only return values to the right and the older HLOOKUP can only return values below the lookup row.