SheetsLessons
Lesson 18 · Lookups

XLOOKUP Function

Practice XLOOKUP in Google Sheets: look left, return a default when nothing is found, and find the last match.

Task 1 of 3

Find the name of the person whose badge number is in E2. The name is to the left of the badge column, which VLOOKUP can't do. Answer in F2.

F2
fx

Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.

XLOOKUP: the modern lookup

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

Instead of one table and a column number, XLOOKUP takes two ranges: where to search, and what to return. That makes it simpler to read and lets it look in any direction, including to the left.

  • =XLOOKUP(E2, C2:C9, A2:A9) finds E2 in column C and returns column A.
  • missing_value replaces #N/A: =XLOOKUP(D2, A2:A9, B2:B9, "Unknown")
  • search_mode -1 searches bottom-up, which returns the last match (great for "latest order").

Exact match is the default, so there's no FALSE to forget.