How to Use XLOOKUP in Excel – Step-by-Step Tutorial for Beginners

About this video

This is a beginner step-by-step tutorial on Excel’s XLOOKUP function, using Excel in Microsoft 365 — Kevin notes XLOOKUP will not work in Excel 2016 or 2019 but will work in Excel 2021. He starts with a vertical lookup: a list of client names and order amounts in columns A and B, and a separate list of names needing amounts filled in. The formula takes a lookup value (the client name cell), a lookup array (the names in column A), and a return array (the amounts in column B), returning $592.11 for TechNova Solutions in his example. He then walks through the optional parameters: if_not_found (he uses 0 as a fallback), match mode (0 for exact match, with next-smaller, next-larger, wildcard and regex options), and search mode (including -1 to search last to first). A key troubleshooting lesson follows: copying the formula without locking the arrays shifts the references and produces wrong results flagged by a green warning; selecting each array and pressing F4 adds dollar signs to lock it. Finally he demonstrates a horizontal lookup across sheets, pulling May prices by matching client-name column headers against a row of prices on an Order History tab, again locking the arrays with F4 before filling right.

Key takeaways

• XLOOKUP needs three core arguments: lookup value, lookup array, and return array; it works in Excel 2021 and Microsoft 365, not Excel 2016 or 2019
• Use the optional if_not_found argument, such as 0, to control what appears when no match is found instead of an error
• Match mode 0 means exact match; other modes offer next smaller/larger, wildcard, and regular-expression matching, and search mode -1 searches last to first
• Lock the lookup and return arrays with F4 (dollar signs) before copying a formula, or shifted references will silently omit rows and return wrong values
• XLOOKUP works horizontally and across sheets too — match a column header against a header row and return values from a price row, then fill right

Episode details: published January 22, 2025 · 8:18 long. Summary written by Self Growth Videos from the episode itself. Business results and any income figures mentioned are Kevin Stratvert’s own claims, shared for education — they are not typical or guaranteed.

← Previous: DaVinci Resolve Fusion Tutorial – Learn Motion Graphics & VFX · Next: Google NotebookLM Tutorial →

All Kevin Stratvert videos · Kevin Stratvert profile · Kevin Stratvert on YouTube