Lookup & Reference

XLOOKUP Function in Excel

Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, XLOOKUP can return the closest (approximate) match.

Syntax

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

Arguments

  • lookup_value (required): The value to search for
  • lookup_array (required): The array or range to search
  • return_array (required): The array or range to return
  • if_not_found (optional): Value to return if no match found
  • match_mode (optional): 0=exact, -1=exact or smaller, 1=exact or larger, 2=wildcard
  • search_mode (optional): 1=first to last, -1=last to first, 2=binary ascending, -2=binary descending

Examples

  • =XLOOKUP("Apple", A2:A10, B2:B10) - Find the price of Apple in a product list - Result: $1.50
  • =XLOOKUP(1001, Orders[ID], Orders[Total], "Not Found") - Look up order total with error handling - Result: $250.00
  • =XLOOKUP(D2, A:A, B:B,, -1) - Find approximate match (smaller) - Result: Matched value
  • =XLOOKUP("*phone*", A2:A20, B2:B20,, 2) - Wildcard search for products containing 'phone' - Result: iPhone 15

Overview

  • XLOOKUP is the default recommendation for new workbooks in Microsoft 365. Learn the six arguments that matter, how match_mode changes results, and when your team still needs VLOOKUP.

Arguments you will actually use

  • Basic: =XLOOKUP(E2,A:A,C:C) — exact match by default.
  • Friendly miss: =XLOOKUP(E2,A:A,C:C,"Not found").
  • Last match: add search_mode -1 when the list is unsorted and you need the last occurrence.

Version and rollout checklist

  • Confirm every recipient has Excel 2021 or Microsoft 365 desktop.
  • Pilot on a copy of the sheet before replacing hundreds of VLOOKUPs.
  • Document which columns map to lookup_array vs return_array.

Next steps in this cluster

  • [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/) decision guide.
  • [XLOOKUP tutorial](/blog/xlookup-excel-tutorial/) step-by-step.
  • Troubleshoot misses: [XLOOKUP not finding value](/problems/xlookup-not-finding-value/).

Quick answer: xlookup excel

  • XLOOKUP searches a column or row and returns a matching value. It replaces most VLOOKUP cases in Excel 2021 and Microsoft 365, including left lookups and custom not-found text.
  • Formula: =XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)
  • Available in Excel 2021 and Microsoft 365 — not in Excel 2019.
  • Default is exact match (match_mode 0).
  • Use if_not_found instead of IFERROR once the lookup logic is correct.

When to use XLOOKUP

  • Use XLOOKUP when you need an exact match lookup that can return values from either side of the lookup column.
  • Use the if_not_found argument to avoid wrapping every lookup in IFERROR.
  • Use match_mode and search_mode when you need wildcard matching, reverse search, or binary search on sorted data.

Microsoft 365 vs Excel 2021 availability

  • XLOOKUP is available in Microsoft 365 and Excel 2021 or later on desktop.
  • It is not available in Excel 2019, Excel 2016, or Excel for the web in all tenants — verify before deploying to a mixed team.
  • For shared legacy files, keep VLOOKUP or INDEX MATCH until all users have a compatible build.

match_mode and search_mode examples

  • Exact match (default): =XLOOKUP(E2,A:A,C:C) for product codes and employee IDs.
  • Wildcard match: =XLOOKUP("*"&E2&"*",A:A,C:C,,2) when partial text matching is required.
  • Last match: set search_mode to -1 when the lookup column is unsorted and you need the last occurrence.
  • Binary search: use match_mode -1 or 1 only when the lookup column is sorted ascending or descending.

Related learning path

  • Pair with [INDEX](/functions/index/) and [MATCH](/functions/match/) for two-way models.
  • Read [INDEX MATCH vs XLOOKUP](/compare/index-match-vs-xlookup/) and the [XLOOKUP tutorial](/blog/xlookup-excel-tutorial/).
  • Troubleshoot misses in [XLOOKUP not finding value](/problems/xlookup-not-finding-value/).

Step-by-step: replace one VLOOKUP with XLOOKUP

  • 1. Identify lookup_value, first column of old table, and return column.
  • 2. Point lookup_array at the old key column and return_array at the return column.
  • 3. Add if_not_found text only after the match logic is verified.
  • 4. Test one row, then fill down.

People also ask

  • Is XLOOKUP better than VLOOKUP? — Usually yes in Excel 2021/M365 for flexibility.
  • Does XLOOKUP work in Excel 2019? — No on desktop; keep VLOOKUP for legacy files.

Common errors

  • #N/A when no match is found
  • #VALUE! when arrays have different sizes
  • #REF! when referencing invalid range

Use cases

  • Product price lookups
  • Employee data retrieval
  • Inventory management
  • Financial reporting

Frequently asked questions

  • Is XLOOKUP in Excel 2019? No on desktop Excel 2019. It is available in Excel 2021 and Microsoft 365 subscriptions.
  • What is match_mode in XLOOKUP? 0 = exact match (default for IDs). 2 = wildcard. -1 and 1 are binary search modes and require sorted lookup columns.
  • Can XLOOKUP replace IFERROR(VLOOKUP(...))? Yes — use the if_not_found argument after the lookup logic is correct. Do not use it to mask a broken match.
  • What is XLOOKUP in Excel and how does it work? XLOOKUP is a modern lookup function in Excel that searches for a value in a range and returns a corresponding value from another range. It works by taking a lookup_value, searching for it in the lookup_array, and returning the matching value from the return_array. Unlike VLOOKUP, XLOOKUP can search in any direction and includes built-in error handling.
  • What is the difference between XLOOKUP and VLOOKUP? XLOOKUP is more powerful than VLOOKUP in several ways: 1) XLOOKUP can look left or right, while VLOOKUP only looks right; 2) XLOOKUP has a built-in if_not_found parameter for error handling; 3) XLOOKUP doesn't require counting columns; 4) XLOOKUP defaults to exact match, while VLOOKUP defaults to approximate; 5) XLOOKUP can return entire rows or columns of data.
  • How do I use XLOOKUP with multiple criteria? To use XLOOKUP with multiple criteria, concatenate your lookup values and lookup arrays. For example: =XLOOKUP(A1&B1, C:C&D:D, E:E) combines values from columns A and B to search in combined columns C and D. Alternatively, use nested XLOOKUP functions or combine with FILTER for complex multi-criteria lookups.
  • Why is my XLOOKUP returning #N/A error? XLOOKUP returns #N/A when it cannot find the lookup value. Common causes include: 1) The value doesn't exist in the lookup range; 2) Extra spaces in the data (use TRIM to fix); 3) Different data types (text vs number); 4) Case sensitivity issues. Use the if_not_found parameter to handle this: =XLOOKUP(value, range, return, "Not Found").
  • Is XLOOKUP available in all Excel versions? XLOOKUP is available in Excel 365, Excel 2021, and Excel for the web. It is NOT available in Excel 2019 or earlier versions. If you need compatibility with older versions, use INDEX/MATCH or VLOOKUP instead. Google Sheets has a similar function called XLOOKUP that was added in 2022.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.