Formulas - 2026-05-12

XLOOKUP in Excel: Complete Tutorial

Learn XLOOKUP in Excel with exact match, custom not-found messages, left lookup, approximate match, and practical business examples.

Quick answer: xlookup tutorial

  • XLOOKUP tutorial flow: confirm Excel 2021/M365 → map old VLOOKUP columns to lookup_array and return_array → test one row → add if_not_found.
  • Formula: =XLOOKUP(E2,A:A,C:C,"Not found")
  • Migration checklist included below in this article.
  • Compare [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/).

Overview

  • This tutorial assumes you know VLOOKUP basics and want a modern replacement. You will map old arguments to XLOOKUP and test left lookup plus if_not_found in one sheet.

Migration map (VLOOKUP → XLOOKUP)

  • lookup_value → same cell.
  • First column of table → lookup_array.
  • Return column → return_array.
  • IFERROR → if_not_found argument.

Exercises to complete

  • Replace one vertical VLOOKUP on your sheet.
  • Build a left lookup (name → ID) without moving columns.
  • Add "Not in list" via if_not_found.

Continue learning

  • [XLOOKUP function](/functions/xlookup/)
  • [Compare vs VLOOKUP](/compare/xlookup-vs-vlookup/)
  • [INDEX MATCH guide](/blog/index-match-excel-guide/) for two-way models

What XLOOKUP Does

  • XLOOKUP searches one range and returns a matching value from another range. It is the modern replacement for many VLOOKUP, HLOOKUP, and INDEX MATCH formulas. Basic syntax: \\\ =XLOOKUP(lookup_value, lookup_array, return_array) \\\

Basic XLOOKUP Example

  • If product IDs are in A2:A100 and prices are in C2:C100: \\\ =XLOOKUP(E2,A2:A100,C2:C100) \\\ This searches for E2 in A2:A100 and returns the matching price from C2:C100.

Add a Not Found Message

  • \\\ =XLOOKUP(E2,A2:A100,C2:C100,"Not found") \\\ This is cleaner than wrapping VLOOKUP in IFERROR.

Left Lookup

  • XLOOKUP can return values to the left of the lookup column. \\\ =XLOOKUP(E2,C2:C100,A2:A100) \\\ This searches column C and returns from column A.

Related Guides

  • XLOOKUP function XLOOKUP vs VLOOKUP INDEX MATCH in Excel Excel Lookup Functions Guide

XLOOKUP Pattern Library

  • Use these patterns in real spreadsheets: Goal Formula pattern Exact match =XLOOKUP(E2,A:A,C:C) Custom not found =XLOOKUP(E2,A:A,C:C,"Not found") Left lookup =XLOOKUP(E2,C:C,A:A) Return multiple columns =XLOOKUP(E2,A:A,C:E) Wildcard match =XLOOKUP("*"&E2&"*",A:A,C:C,,2) The built-in not-found argument is one of the biggest reasons XLOOKUP is cleaner than VLOOKUP plus IFERROR.

Expert Model Design Tip

  • Keep lookup arrays and return arrays the same height. Avoid full-column references in very large workbooks if calculation speed matters. Use Excel Tables when possible, such as: \\\ =XLOOKUP([@ProductID],Products[ProductID],Products[Price],"Not found") \\\ This makes the formula more readable and resilient as rows are added.

Related Decision Paths

  • Use XLOOKUP vs VLOOKUP when modernizing older workbooks, INDEX MATCH vs XLOOKUP for complex lookup models, and FILTER when one lookup value can return multiple rows.

Migration checklist from VLOOKUP

  • List every VLOOKUP and its table_array layout. Replace with XLOOKUP only where all users have Excel 2021 or Microsoft 365. Map col_index_num to the correct return_array. Move not-found text into if_not_found instead of IFERROR. Test left lookups and last-match cases separately.

Frequently asked questions

  • Is XLOOKUP better than VLOOKUP? Yes for most modern workbooks. XLOOKUP is easier to read, can search left, and supports a built-in not-found result.
  • Does XLOOKUP require sorted data? No. Exact match XLOOKUP does not require sorted data.
  • Can XLOOKUP return more than one column? Yes. If the return_array spans multiple columns, XLOOKUP can spill multiple returned columns in Microsoft 365.
  • Why is XLOOKUP returning #N/A? The lookup value may not exist, the lookup array may contain a different data type, or hidden spaces may prevent an exact match.