ExcelTool.io

XLOOKUP

The modern replacement for VLOOKUP and HLOOKUP. Searches any array and returns a matching value from another array, in any direction.

Syntax

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

Arguments

  • lookup_value - The value to search for.
  • lookup_array - The array or range to search.
  • return_array - The array or range to return a value from.
  • if_not_found (optional) - Value to return when no match is found (avoids #N/A).
  • match_mode (optional) - 0 exact (default), -1/1 next smaller/larger, 2 wildcard.
  • search_mode (optional) - 1 first-to-last (default), -1 last-to-first.

Examples

=XLOOKUP(A2, D2:D100, F2:F100, "Not found")

Returns: Matching F value, or "Not found"

No column counting, and the return array can be left or right of the lookup array.

Common Errors and Gotchas

  • #NAME? means your Excel version predates XLOOKUP - it needs Microsoft 365 or Excel 2021+. Use INDEX/MATCH instead.
  • lookup_array and return_array must be the same length, or you get #VALUE!.

Does XLOOKUP Work in Google Sheets?

Yes. XLOOKUP uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.

Build a XLOOKUP formula

Fill in your ranges and copy a ready-to-paste formula - no signup.

XLOOKUP Generator

See Also

VLOOKUPINDEXMATCH