How to XLOOKUP Function in Excel

Why This Task Matters in Excel

The moment you start working with tabular data, you quickly realise that answering even the most basic business questions usually involves “looking something up.” Which product matches this code? Which salesperson owns this account? What is the correct price for this size and colour combination? In accounting, finance, sales operations, supply-chain logistics, HR analytics, and countless other disciplines, matching a lookup value to its corresponding result drives reports, dashboards, and day-to-day decision making.

Historically, functions such as VLOOKUP, HLOOKUP, and later INDEX-MATCH were your go-to tools. They still work, yet they each carry hidden costs: rigid column order, breakable insertions or deletions, mental overhead when remembering column indexes, and limited default error handling. Microsoft responded by introducing the XLOOKUP function to Microsoft 365 and Excel 2021, delivering a modern, flexible, and more intuitive way to “search one thing and return another.”

Because XLOOKUP combines vertical and horizontal lookups, supports reverse searches, tolerates insertions, offers near-instantaneous spill ranges, and includes built-in error messaging, it reduces formula complexity while boosting resiliency. A single XLOOKUP can replace nested IFs, a VLOOKUP wrapped in IFERROR, or a multi-step INDEX-MATCH construction. Mastering it therefore unlocks immediate productivity gains, lowers maintenance risk, and future-proofs your files.

Consider the practical impact:

• In a sales forecast workbook, XLOOKUP instantly pulls the correct unit price using multiple possible SKU aliases, preventing costly quoting errors.
• In HR onboarding dashboards, it cross-references employee IDs across sheets to return start date, department, and manager in one step, keeping everything aligned with payroll exports.
• In inventory management, you can write a single formula that retrieves a product’s stock level “from right to left,” something VLOOKUP simply cannot do.

Failing to know XLOOKUP means you may cling to fragile formulas that break when someone inserts a column, or you might spend hours debugging #N/A errors after a data refresh. Moreover, learning XLOOKUP deepens your comprehension of Excel’s dynamic array engine, paving the way for functions like FILTER, SORT, and UNIQUE—skills that push your analytical capabilities well beyond simple lookups.

Best Excel Approach

When your goal is “match this value and return that result,” XLOOKUP is usually the best-in-class solution. It is flexible, readable, and forwards-compatible with dynamic arrays. Behind the scenes it searches a lookup array for a lookup value, then returns the corresponding item from a return array of identical size. You can control what happens when no match exists, choose exact or approximate matching, and force the search to run from bottom-up if desired. All of these options live inside one elegant function—no helper columns, no brittle index numbers, and no nested wrappers.

Use XLOOKUP when:

• Your file sits in Microsoft 365 or Excel 2021 (earlier versions do not support it).
• You want immunity from column insertions or deletions.
• You need both vertical and horizontal lookup options without rewriting logic.
• You prefer built-in error messaging over IFERROR wrappers.
• You must perform “last value” or reverse searches quickly.

Reserve alternatives only when you face backward-compatibility requirements or special multi-criteria scenarios that demand INDEX with MATCH on arrays (though even there, XLOOKUP can join criteria). The function’s essential logic is:

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

Parameter explanations:

• lookup_value – the value you are searching for (number, text, date, logical).
• lookup_array – one-dimensional range where the match is attempted.
• return_array – one-dimensional range from which the result will be extracted.
• [if_not_found] – optional text or expression returned when no match exists.
• [match_mode] – 0 for exact match (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
• [search_mode] – 1 first-to-last (default), -1 last-to-first, 2 binary search ascending, -2 binary search descending.

Parameters and Inputs

Because XLOOKUP’s power hinges on proper inputs, invest a moment to validate what flows into each argument:

• lookup_value should match the data type of items in lookup_array. A number formatted as text will fail to match a numeric cell. Use VALUE, TEXT, or data cleansing to align types.
• lookup_array and return_array must share identical shapes: both single columns or both single rows of equal length. Mismatched sizes trigger the #VALUE! error.
• [if_not_found] accepts text in quotes, cell references, numeric defaults, or even another formula such as NA(). Many users set this to \"\" (blank) to hide errors, yet descriptive messages like \"Not in list\" often aid debugging.
• [match_mode] exact match (0) is safest for textual keys. Approximate match (-1 or 1) requires sorted data and is common in grading thresholds or pricing brackets. Wildcard (2) allows \"AB*\" to match any code starting with AB, but be aware that ? and * characters behave as wildcards only when match_mode = 2.
• [search_mode] defaults to top-down (1). Bottom-up (-1) is invaluable for pulling the latest transaction in a running log. Binary search modes (2 or -2) provide speed on very large, sorted datasets.

Data preparation tips:

• Remove trailing spaces and unify case when matching text; TRIM and UPPER are your friends.
• Ensure there are no blank cells inside lookup_array: blanks can unintentionally match blank lookup_value inputs.
• Consider converting source data to an official Excel Table. Structured references like Table1[SKU] automatically resize, keeping lookup and return arrays perfectly aligned.

Edge-case handling:

• Duplicate keys return the first or last hit based on search_mode.
• Dates stored as serial numbers versus text produce mismatches—use DATEVALUE to standardise input.

Step-by-Step Examples

Example 1: Basic Scenario – Price Lookup in an Order Form

Imagine a simple order form where column [A] contains product codes typed by a salesperson, and a reference table in columns [E:F] lists each code alongside its unit price.

Sample data

A2:A7 – Order lines: P-100, P-200, P-300, (blank), P-500, P-900  
E2:E6 – Product codes: P-100, P-200, P-300, P-400, P-500  
F2:F6 – Unit price: 12.50, 15.00, 9.25, 11.00, 19.80

Step-by-step:

1. In cell [B2] next to the first order line, enter the formula:

=XLOOKUP(A2, $E$2:$E$6, $F$2:$F$6, "Not listed")

2. Copy the formula down to [B7].
3. Observe that rows with blank input remain blank because XLOOKUP treats empty lookup_value as valid. You can enhance with IF(A\2=\"\",\"\",XLOOKUP(...)) to suppress that behaviour.
4. Type a new product code P-400 in [A4]; the list price instantly appears because our lookup and return arrays cover that code.
5. Add a new product row P-900 / 27.50 to the reference list. Because arrays use absolute cell references, you must extend them, or—better—convert the reference list into a Table named Prices. Then rewrite:

=XLOOKUP(A2, Prices[Code], Prices[Unit_Price], "Not listed")

Why this works
XLOOKUP scans the Codes column until it finds A2, aligns the row index, and fetches the corresponding Unit_Price. If no match exists, the custom message \"Not listed\" appears. Because we are using Tables, subsequent additions expand automatically.

Troubleshooting tips

• If every line shows #N/A, verify that A2’s data type equals Prices[Code]’s data type.
• If only certain lines error, check for trailing spaces or inconsistent hyphen styles (- vs -).

Example 2: Real-World Application – Employee Dashboard with Cross-Sheet Lookup

Scenario
You manage an HR dashboard summarising workforce demographics. Sheet Employees contains a master list with columns: Emp_ID, Department, Start_Date, Manager. On a separate sheet called Summary, you need to display Department and Manager given an Emp_ID typed by the user in cell [B2].

Data setup (simplified)
Employees[Emp_ID] – 1001, 1002, 1003, 1004 …
Employees[Department] – Sales, Finance, Marketing, IT …
Employees[Manager] – Perez, Doyle, Lin, Foster …

Walkthrough

1. In Summary sheet cell [B4] (labelled Department), enter:

=XLOOKUP($B$2, Employees[Emp_ID], Employees[Department], "ID not found")

2. In cell [B5] (labelled Manager), enter:

=XLOOKUP($B$2, Employees[Emp_ID], Employees[Manager], "ID not found")

3. Set [B2] data validation to Whole Number between the minimum and maximum Emp_ID to reduce typos.
4. Name the range [B2] \"Selected_ID\" to make formulas self-documenting if you prefer:

=XLOOKUP(Selected_ID, Employees[Emp_ID], Employees[Manager], "ID not found")

Integration with other Excel features

• Conditional Formatting: highlight [B4] and [B5] red when they return \"ID not found\".
• SORT and FILTER: build a dynamic table of all employees that roll up to the manager returned in [B5].
• Charts: create a tenure histogram by using XLOOKUP to fetch Start_Date then calculating DAYS /365 for each selected ID.

Performance consideration
Even with thousands of rows, XLOOKUP’s speed rivals VLOOKUP and INDEX-MATCH, especially when you restrict the lookup_array to a Table column rather than applying entire column references.

Example 3: Advanced Technique – Multi-Criteria Lookup with Spilled Array

Challenge
Retrieve the latest revenue figure for each salesperson across multiple periods, even if each person appears several times in the dataset. This requires joining criteria (name plus month) and searching bottom-up.

Dataset (Table Sales):
Sales[Rep] – Adams, Baker, Clark, Adams, Baker, Clark
Sales[Month] – Jan, Jan, Jan, Feb, Feb, Feb
Sales[Revenue] – 12,000; 9,500; 10,800; 11,300; 12,400; 9,900

Goal
Build a summary in columns [H:I] that lists each unique Rep and their Feb revenue, spilling automatically when new reps appear.

Steps

1. Generate the unique list of reps in [H2]:

=UNIQUE(Sales[Rep])

2. In [I2], spill the matching revenue with a two-dimension XLOOKUP:

=XLOOKUP([H2#]&"Feb", Sales[Rep]&Sales[Month], Sales[Revenue], "Missing", 0, -1)

Explanation

• We concatenate Rep and Month to create a composite key.
• The lookup_value is itself a spilled array [H2#] joined to \"Feb\", producing [\"AdamsFeb\",\"BakerFeb\", …].
• search_mode -1 forces bottom-up search so that if multiple Feb entries exist, the formula returns the latest one.
• The entire formula spills down, populating revenue for every unique rep without copying.

Edge-case handling
If a rep has no Feb entry, \"Missing\" appears. You could wrap that result in IFERROR or even fallback to January using a nested XLOOKUP.

Performance optimisation
Dynamic arrays evaluate once, then feed into XLOOKUP; this is faster than invoking XLOOKUP separately for each row in legacy functions.

Professional tip
For extremely wide composite keys, use TEXTJOIN to concatenate multiple fields with a delimiter, ensuring no accidental overlaps (e.g., \"Adam\" + \"sF\" + \"eb\" could equal someone else’s combination).

Tips and Best Practices

1. Convert source data to Tables. Structured references like Sales[Revenue] auto-resize and keep XLOOKUP arguments in sync, virtually eliminating range misalignment errors.
2. Explicitly set [if_not_found] to a helpful message or 0. Avoid blank results that mask data quality issues.
3. For large models, restrict lookup_array to realistic bounds rather than entire columns—this prevents unnecessary recalculation.
4. Use search_mode -1 to capture the most recent entry in transactional logs. A reverse search can replace complex MAXIFS approaches.
5. Document match_mode choice in adjacent comments. Approximate lookup requires sorted data; if someone later resorts the table, the formula may silently return wrong values.
6. Combine XLOOKUP with dynamic arrays—UNIQUE, FILTER, SORT—to build interactive, formula-only dashboards without VBA or Power Query.

Common Mistakes to Avoid

1. Mismatched array sizes. If lookup_array has 1,000 rows but return_array has 999, XLOOKUP throws #VALUE!. Confirm dimensions with COUNTA on both columns.
2. Type mismatches. Text \"1001\" never equals numeric 1001. Pre-clean data with VALUE or TEXT, or coerce inside the formula: XLOOKUP(VALUE(A2), …).
3. Forgetting absolute references in traditional ranges. If you copy a formula downward without $E$2:$E$6 anchors, lookup_array can shift and misalign. Tables solve this elegantly.
4. Omitting [if_not_found] then wondering why #N/A appears. New users often panic at errors, yet they could have displayed \"Not found\" and focused on root causes.
5. Using approximate match on unsorted data. This yields unpredictable outputs. The cure is either sorting ascending (for match_mode -1) or specifying exact match.

Alternative Methods

For migration, wrap legacy VLOOKUP in XLOOKUP one sheet at a time. Keep a backup, validate outputs with quick SUM comparisons, then phase out older functions.

FAQ

When should I use this approach?

Whenever you need a one-for-one lookup—search a key and return a single corresponding value—XLOOKUP is ideal. It is especially powerful when you might later insert columns, need to search from bottom-up, or require a friendly message on failure.

Can this work across multiple sheets?

Yes. Simply reference the lookup_array and return_array on the source sheet:

=XLOOKUP(A2, 'Product Data'!$A:$A, 'Product Data'!$D:$D, "No match")

The formula recalculates instantly when the other sheet updates. Combine with 3-D referencing or structured tables for even cleaner syntax.

What are the limitations?

XLOOKUP is unavailable in Excel 2019 and earlier. It also cannot natively spill multiple matches (use FILTER for that). Duplicate keys return only the first or last match, not all. Binary search modes need sorted data, otherwise incorrect results appear.

How do I handle errors?

Leverage the [if_not_found] argument rather than wrapping in IFERROR. For generic numeric fallbacks, supply 0; for user-facing dashboards, provide descriptive text. You can still wrap XLOOKUP in IF to intercept blank lookup values.

Does this work in older Excel versions?

No. XLOOKUP requires Microsoft 365 or Excel 2021. For compatibility with Excel 2016 and earlier, fall back to INDEX-MATCH or VLOOKUP. A hybrid workbook can contain both formula sets, but be cautious of circularity.

What about performance with large datasets?

XLOOKUP is implemented in the new calculation engine and often outperforms VLOOKUP. For multi-million-row scenarios, use binary search modes with sorted data or move heavy lifting to Power Query / Power Pivot. Keep lookup_array to the minimum needed range to cut scan time.

Conclusion

Mastering XLOOKUP is one of the fastest ways to modernise your Excel skill set. It slices through common lookup challenges with clean syntax, safeguards your models against structural changes, and dovetails perfectly with dynamic arrays. Whether you are building interactive dashboards, ad-hoc reports, or enterprise-scale workbooks, XLOOKUP delivers accuracy, resilience, and clarity. Push yourself to replace older VLOOKUP constructions gradually, explore composite keys and reverse searches, and soon the simplicity of XLOOKUP will become second nature—freeing you to focus on deeper analysis and insight generation.