Skip to content
Google Sheets

XLOOKUP function in Google Sheets

Returns the values in the result range based on the position where a match was found in the lookup range. If no match is found, it returns the closest match.

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

XLOOKUP syntax and parameters

Six arguments, three required.

  • search_keyanyRequired

    The value to search for. For example, 42, "Cats", or B24.

  • lookup_rangerangeRequired
  • result_rangerangeRequired
  • missing_valueanyOptional

    [OPTIONAL - #N/A by default] The value to return if no match is found.

  • match_modenumberOptional

    [OPTIONAL - 0 by default] The manner in which to find a match for the search_key. 0: For an exact match. 1: For an exact match or the next value that is greater than the search_key. -1: For an exact match or the next value that is lesser than the search_key. 2: For a wildcard match.

  • search_modeanyOptional

XLOOKUP examples

Formulas you'll actually reuse.

  1. Price lookup that says so when the product is missing:

    =XLOOKUP(E2, A2:A200, C2:C200, "Not stocked")

    Result24.99

  2. Search from the bottom to get the latest order:

    =XLOOKUP(E2, B2:B200, D2:D200, , 0, -1)

    Result2026-08-14

XLOOKUP in Excel

Same name — your formula ports as-is.

Try XLOOKUP in the playground

Edit the example — nothing to install.

Preloaded with the XLOOKUP formula from Example 1 — change anything and watch it respond.

Loading the editor…

XLOOKUP errors

What they mean — and the fixes.

  • #N/A

    No match and no missing_value fallback — pass a fourth argument so missing lookups read as data, not errors.

  • #VALUE!

    lookup_range and result_range have different lengths — both must span the same number of rows or columns.

Related functions

More ways Google Sheets gets this done.