SheetsLessons
Lookup function

XLOOKUP function in Google Sheets

XLOOKUP finds a value in one range and returns the item in the same position from another range. It does everything VLOOKUP does, and it can look left, has a built-in "not found" value, and can search from the bottom up.

Syntax

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
search_key
The value to find.
lookup_range
A single row or column to search.
result_range
The row or column to return from. Same size as lookup_range.
missing_value
Optional. Returned instead of #N/A when nothing matches.
match_mode
Optional. 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
search_mode
Optional. 1 first to last (default), -1 last to first.

Practice XLOOKUP

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 examples

Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.

ABCDE
1Emp IDNameDeptEmailSalary
21042Maya PatelFinancemaya.patel@acme.co$72,000.00
31187Leo RossiSupportleo.rossi@acme.co$51,000.00
41305Ava KimSalesava.kim@acme.co$64,000.00
51411Omar AliMarketingomar.ali@acme.co$58,000.00
61520Hana TanakaSaleshana.tanaka@acme.co$69,000.00
Employee directory
FormulaResultWhat it shows
=XLOOKUP("Ava Kim", B2:B6, A2:A6)1305Looks left: name → ID.
=XLOOKUP("Sales", C2:C6, B2:B6)Ava KimFirst match from the top.
=XLOOKUP("Sales", C2:C6, B2:B6, , 0, -1)Hana Tanakasearch_mode -1 returns the last match.
=XLOOKUP(9999, A2:A6, B2:B6, "Not found")Not foundBuilt-in fallback instead of #N/A.

Tips and common mistakes

  • Exact match is the default, so there's no FALSE to forget.
  • Because you point at the result column directly, inserting columns never breaks the formula.
  • To skip an optional argument, leave it empty between commas, as in the third example.

Related functions

VLOOKUPSearches the first column of a range and returns a value from the same row.INDEXReturns the cell at a given row and column of a range.MATCHReturns the position of a value in a row or column.HLOOKUPSearches the first row of a range and returns a value from the same column.

Learning step by step? XLOOKUP is covered in Lesson 18: XLOOKUP Function of the free course.