New XLOOKUP Function in Excel

Access this expert-led webinar instantly, available anytime on-demand.

4.6
Included in All-Access Membership
Live Webinar - no upcoming date
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.

Purchase Options

Select an attendee quantity to add to cart.

Recorded Webinar Only

$219.00
or

All Access Membership

The Aurora All Access Membership is designed to provide you with the training that you want when you want it. You will have 100% access to every live webinar, on demand webinar, professional alert, and podcast that Aurora Training Advantage offers with no additional cost.

Learn More About Our All Access Membership
$599.00
All Access Membership

Microsoft introduced the XLOOKUP function to Office 365 in August 2019, providing a powerful new approach to data lookup and retrieval in Excel. As with many Office 365 features, XLOOKUP was released in phases and is available through Microsoft’s subscription-based Office 365 platform. In this informative webinar, David provides a practical overview of XLOOKUP and demonstrates how it compares to traditional lookup methods, including VLOOKUP, HLOOKUP, and INDEX/MATCH. Participants will also explore the similarities between XLOOKUP and Excel’s legacy LOOKUP function.

Designed for Excel users seeking to improve spreadsheet accuracy and efficiency, this session features David’s step-by-step teaching approach. Each technique is demonstrated twice—first through clearly numbered PowerPoint slides and then directly within the Office 365 version of Excel. Throughout the presentation, David highlights key differences between Office 365 and perpetual-license versions of Excel, including Excel 2019, Excel 2016, Excel 2013, and earlier releases. Attendees will also receive detailed handouts and an accompanying Excel workbook containing many of the examples demonstrated during the webcast.

Your Benefits For Attending:
  • Identify the purpose of Excel’s XLOOKUP function and understand how it compares to VLOOKUP, HLOOKUP, INDEX/MATCH, and the legacy LOOKUP function.
  • State the purpose of the column_index_num argument within VLOOKUP and recognize how XLOOKUP simplifies lookup formulas.
  • Use XLOOKUP to perform exact-match, approximate-match, and left-side lookups, as well as return multiple values from a cell range.
  • Contrast the MATCH function with the newer XMATCH function and understand what MATCH returns when a lookup_value is found.
  • Identify the number of criteria pairs that can be specified in the MAXIFS function and gain insight into newer Excel features available through Office 365.

This webinar provides practical, hands-on guidance for using Excel’s modern lookup tools more effectively. By attending, you will strengthen your understanding of lookup functions, improve spreadsheet accuracy, and learn techniques that can help streamline everyday Excel tasks.

Topics Typically Covered:
  • Determining if you have the subscription-based Office 365 version of Excel or a perpetually licensed version.
  • Reviewing the LOOKUP function and its limitations.
  • Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
  • Using the HLOOKUP function to look across rows instead of down columns.
  • Contrasting INDEX/MATCH to VLOOKUP.
  • Introducing the XLOOKUP worksheet function.
  • Using XLOOKUP to find the last match.
  • Using XLOOKUP to perform lookups to the left.
  • Understanding exact-match versus approximate-match behavior.
  • Returning multiple values from a lookup result.
  • Using wildcard support within XLOOKUP.
  • Finding approximate matches without sorting data.
  • Contrasting MATCH and XMATCH functions.
  • Exploring the Microsoft Excel Insider program.
Level: Basic
Format: Live Webcast
Instructional Method: QAS Self-Study (Traditional)
NASBA Field of Study: Computer Software & App (2 hours)
Program Prerequisites: None
Advance Preparation: No
  1. Introduction
  2. Please Ask Questions Today 00:02:31
  3. Excel Versions 00:04:17
  4. Office Insider Program 00:06:04
  5. MAC Office Insider Program 00:10:17
  6. VLOOKUP Introduction 00:10:47
  7. MATCH Introduction 00:17:15
  8. INDEX/MATCH Introduction 00:21:50
  9. XMATCH Introduction 00:26:27
  10. LOOKUP Introduction 00:31:01
  11. XLOOKUP Introduction (Office 365) 00:34:55
  12. Excel For MAC Function Screen Tips 00:38:47
  13. VLOOKUP with IFERROR 00:40:05
  14. XLOOKUP If_Not_Found Error Argument 00:44:18
  15. XLOOKUP Blank Cells Cause #N/A 00:47:37
  16. HLOOKUP Introduction 00:51:55
  17. XLOOKUP Lookup Horizontally 00:57:17
  18. XLOOKUP Approximate Matches 00:58:22
  19. XLOOKUP Wildcard Nuances 01:02:12
  20. VLOOKUP With CHOOSE Function 01:05:49
  21. XLOOKUP Looking To The Left 01:09:23
  22. XLOOKUP Finding The Last Match 01:11:01
  23. XLOOKUP Matching Multiple Criteria 01:13:57
  24. XLOOKUP Can Return Multiple Columns 01:19:25
  25. XLOOKUP Summing Multiple Columns 01:24:50
  26. XLOOKUP Text Vs. Number Causes #N/A 01:27:23
  27. Correcting Numbers Stored As Text 01:29:21
  28. XLOOKUP Duplicate Data Trap 01:30:49
  29. SUMIF Introduction 01:33:00
  30. SUMIFS Introduction 01:36:35
  31. XLOOKUP Summing Multiple Columns 01:39:46
  32. Thanks For Attending! 01:41:10
  33. Presentation Closing 01:41:37
  • David H. Ringstrom, CPA

    David H. Ringstrom, CPA

    David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in E [...]

    View Full Profile

ATAAA Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in administrative.

ATATX Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.

ATAOP Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or [email protected].

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.

  • #N/A Error 00:19:48 
  • CHOOSE Function 01:06:05
  • Column 00:17:57, 01:19:28
  • Formula 00:20:15
  • Helper Column 01:14:12
  • HLOOKUP 00:52:01
  • IFERROR Function 00:40:26
  • IFNA Function 00:44:40
  • INDEX Function 00:18:31, 00:21:59
  • LOOKUP 00:31:01
  • MATCH Function 00:17:16, 00:21:55
  • Office 365 00:02:09, 00:04:31
  • Row 00:17:56
  • SUM 01:25:00
  • SUMIF 01:33:01
  • SUMIFS Function 01:33:04, 01:36:37
  • Table Array 00:12:17
  • Text to Columns Wizard 01:29:30
  • VLOOKUP 00:01:33, 00:10:54, 00:31:45, 01:10:09, 01:14:07
  • Wildcards 00:59:13, 01:02:14
  • XLOOKUP 00:01:02, 00:34:55, 00:51:09, 00:57:18, 01:09:26
  • XMATCH Function 00:26:27, 01:11:06

#N/A Error: Excel displays this error when a lookup function, such as VLOOKUP or MATCH, cannot return the requested information.

CHOOSE Function : The CHOOSE function allows you to return a specified item from a list, but in certain cases, it also can be used to have VLOOKUP return data from the left of its criteria column.

Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.

Formula: A formula is an expression which calculates the value of a cell.

HLOOKUP: HLOOKUP is an Excel function to lookup and retrieve data from a specific row in table. The "H" in HLOOKUP stands for "horizontal", where lookup values appear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, and wildcards (* ?) for finding partial matches.

Helper Column: A helper column is a non-technical term to describe a column added to a set of data to help simplify a complex formula or an operation that would be otherwise difficult. You can use VLOOKUP to perform a lookup with multiple criteria by adding a helper column to the data.

IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.

IFNA Function : Introduced in Excel 2013, the IFNA function allows users to display alternative results for a calculation that results in a #N/A error. The IFNA function will, however, reveal other errors, such as #REF!, #NULL!, etc. IFNA isn’t backward compatible with Excel 2010 and earlier.

INDEX Function: The INDEX function can be used to return data from within a given range based on a row and/or column number that you specify.

LOOKUP: The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel.

MATCH Function: The MATCH function searches a prescribed range for specified criteria and returns a column or row number if a match is found. MATCH can be used with other functions that require a column or row number.

Office 365: Office 365 combines the familiar Microsoft Office desktop suite with cloud-based versions of Microsoft's next-generation communications and collaboration services—including Microsoft Exchange Online, Microsoft SharePoint Online, Office for the web, and Microsoft Skype for Business Online—to help users be productive .

Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.

SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet

SUMIF: A look-up function in Excel that allows you to add up numbers based upon a criterion that you specify. Unlike VLOOKUP, the SUMIF function can add up two or more values and returns zero (instead of #N/A) if no match is found.

SUMIFS Function : A look-up function in Excel that allows you to add up numbers based upon up to 127 criteria that you specify. Unlike VLOOKUP, the SUMIFS function can add up two or more values and returns zero (instead of #N/A) if no match is found.

Table Array: A table array is one of the arguments used in Excel's lookup functions, such as VLOOKUP and HLOOKUP. For VLOOKUP (vertical lookup), the table_array must contain at least two columns of data. For HLOOKUP (horizontal lookup), the table_array must contain at least two rows of data.

Text to Columns Wizard: An Excel feature which allows users to separate data from a single column within an Excel spreadsheet into two or more columns, or to remove unnecessary data from within a column.

VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.

Wildcards: Wildcards are special characters that can take any place of any character. There are three wildcard characters in Excel: * (asterisk), ? (question mark), and ~ (tilde) .

XLOOKUP: The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. Where a valid match is not found, return the [if_not_found] text you supply.

XMATCH: The XMATCH function in Microsoft Excel allows us to find the relative position within a data array of a specific entry. Microsoft introduced the XMATCH function in a 2019 update where it was described as a successor of the MATCH function.

Customer Satisfaction Guarantee
Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.

Webinar Survey Overall Rating

This webinar received a total of 10 survey responses. Attendees have given an average rating of 4.6 stars out of a possible 5, reflecting the quality and value of the content presented.

Average rating

4.6 / 5
Webinar Presentation
How many of the objectives of the event were met?
4.5 Stars
How useful was the information presented at this event?
4.7 Stars
Overall, how satisfied were you with this event?
4.5 Stars
Speaker Performance
Overall, how satisfied were you with this presenter?
4.8 Stars
How closely did the presenter follow the schedule?
4.5 Stars

Reviews From Webinar Survey

Our webinars are crafted to deliver exceptional value and insight to business professionals. Below, you'll find genuine feedback from attendees.

Tom S.
May 1, 2020
5.0 / 5
Webinar Rating:
5.0 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
no comment
Brandi C.
May 1, 2020
4.6 / 5
Webinar Rating:
4.3 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
Learned a lot.
Christine C.
May 1, 2020
5.0 / 5
Webinar Rating:
5.0 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
no comment
William F.
April 30, 2020
3.8 / 5
Webinar Rating:
4.0 Stars
Speaker Rating:
3.5 Stars
Do you have any other comments, questions or concerns?
no comment
Yvonne M.
April 30, 2020
5.0 / 5
Webinar Rating:
5.0 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
no comment
Lara B.
April 30, 2020
4.8 / 5
Webinar Rating:
4.7 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
I didn’t know how to ask questions. I didn’t see an icon to ask questions. I emailed Aurora admin twice, once before the webinar, and once during the webinar, and I never got a response.
Tanya S.
April 30, 2020
5.0 / 5
Webinar Rating:
5.0 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
no comment
Sara K.
April 30, 2020
4.4 / 5
Webinar Rating:
4.3 Stars
Speaker Rating:
4.5 Stars
Do you have any other comments, questions or concerns?
Great presenter - appreciated his multitasking ability and be able to answer questions. Note - it would be more beneficial if time is spent on the new tools being presented, i.e. the X-Lookup function vs the "beginners" foundation - maybe have it broken up in two classes one for advanced, one for beginners
Diana J.
April 30, 2020
4.4 / 5
Webinar Rating:
4.0 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
no comment
SARI I.
April 30, 2020
4.0 / 5
Webinar Rating:
4.3 Stars
Speaker Rating:
3.5 Stars
Do you have any other comments, questions or concerns?
The survey button didn't work

Frequently Asked Questions

XLOOKUP is a modern Excel lookup function introduced for Office 365 that addresses many of the well-known limitations of VLOOKUP, making it the preferred choice for data lookups in current versions of Excel. Unlike VLOOKUP, which can only look to the right of the lookup column and requires specifying a column index number that breaks when columns are inserted or moved, XLOOKUP can look in any direction—including to the left of the lookup column. XLOOKUP defaults to an exact match rather than an approximate match, which eliminates a common source of errors in VLOOKUP formulas where the optional fourth argument is accidentally omitted. It also handles errors more gracefully through a built-in if_not_found argument, replacing the need to nest VLOOKUP inside IFERROR. XLOOKUP can return a range of values rather than a single cell, enabling formulas that retrieve entire rows or multiple columns simultaneously. It also supports finding the last match in a list—something VLOOKUP cannot do natively—and offers improved wildcard handling. For Excel users still on perpetual licensed versions such as Excel 2019 or 2016, XLOOKUP is not available; those users should continue with INDEX/MATCH as the closest functional equivalent. Aurora Training Advantage offers expert Excel training webinars that cover XLOOKUP in depth alongside VLOOKUP, INDEX/MATCH, and other essential lookup techniques.
XLOOKUP and INDEX/MATCH both solve the core limitation of VLOOKUP—the inability to look left—but XLOOKUP achieves this with a simpler, single-function syntax that is easier to write, read, and maintain. The INDEX/MATCH combination requires nesting two separate functions: MATCH to find the position of the lookup value and INDEX to return the corresponding value from a different column or row. XLOOKUP accomplishes the same result in one formula by directly accepting the lookup array, return array, and optional parameters including the if_not_found value, match mode, and search mode. XLOOKUP is also more versatile: it can return entire ranges rather than single values, perform both vertical and horizontal lookups interchangeably, and find the last match in a list through its search_mode parameter. The XMATCH function—XLOOKUP's companion function—is the modern replacement for MATCH, offering the same extended capabilities in position-finding scenarios. For users on Office 365 or Microsoft 365 subscriptions, XLOOKUP is the recommended approach for new formulas, while INDEX/MATCH remains a valuable fallback for files that must be compatible with older Excel versions. Professionals who work extensively with data lookups benefit significantly from mastering both approaches and understanding the scenarios where each is most appropriate within their specific Excel environment and organizational context.
One of XLOOKUP's most powerful capabilities compared to VLOOKUP and HLOOKUP is its ability to return a range of values rather than a single cell, enabling a single formula to retrieve multiple columns or rows of data simultaneously. When the return_array argument in XLOOKUP spans multiple columns, the function returns a spilled range—an array of values that automatically populates adjacent cells without requiring the formula to be entered in multiple cells. This spill behavior is a feature of Excel's dynamic array functionality available in Office 365 and Microsoft 365. For example, a single XLOOKUP formula can retrieve a customer's name, address, phone number, and account status all at once by referencing a multi-column return array, replacing what would have previously required four separate VLOOKUP formulas with hardcoded column index numbers. XLOOKUP can also be combined with SUM or other aggregation functions to sum values across multiple matched columns in a single expression. When using XLOOKUP to return multiple columns, be aware of the duplicate data trap—if a lookup value appears multiple times in the lookup array, XLOOKUP returns results for the first match by default, though the search_mode parameter can be adjusted to find the last match instead. This multiple-return capability significantly reduces formula complexity and maintenance burden in spreadsheets that retrieve several attributes from a reference table.
XLOOKUP offers flexible match mode options that give users precise control over how the function handles lookups that do not find an exact match. Unlike VLOOKUP, which defaults to approximate match and requires the lookup data to be sorted when using that mode, XLOOKUP defaults to exact match—a safer starting point that prevents incorrect results from unsorted data. When approximate match behavior is needed, XLOOKUP supports finding the next smaller value, next larger value, or exact match through its match_mode parameter (0 for exact, -1 for next smaller, 1 for next larger, 2 for wildcard). The wildcard match mode in XLOOKUP allows partial text matching using asterisk, question mark, and tilde characters, providing similar functionality to VLOOKUP's wildcard support but with the added flexibility of XLOOKUP's directional lookup and error-handling capabilities. A notable nuance is that XLOOKUP's wildcard behavior has some differences from VLOOKUP's implementation that practitioners should test carefully before relying on in production spreadsheets. The approximate match modes in XLOOKUP are also more powerful than VLOOKUP's equivalent because they do not require the lookup range to be sorted in ascending order, removing a significant source of difficult-to-diagnose errors that have plagued VLOOKUP users for years. Understanding these match mode options is essential for using XLOOKUP effectively across the full range of real-world lookup scenarios encountered in business data analysis.
XLOOKUP is available exclusively in Office 365 and Microsoft 365—the subscription-based versions of Microsoft Excel that receive regular feature updates. It is not available in perpetually licensed versions of Excel such as Excel 2019, Excel 2016, Excel 2013, or earlier, regardless of the operating system. This means that spreadsheets using XLOOKUP will return errors when opened in older versions of Excel, which is an important compatibility consideration for organizations where different users or systems run different Excel versions. Microsoft introduced XLOOKUP for Office 365 subscribers in 2019, rolling it out in waves, meaning even Office 365 users may have received it at different times depending on their update channel settings. Users on the Office Insider program—Microsoft's preview channel for forthcoming features—received access earliest. To determine which version of Excel you are running, check File > Account in Excel, where the product name and version number are displayed. If XLOOKUP is not available in your version, INDEX/MATCH is the closest functional substitute that works across all Excel versions. For professionals who regularly share workbooks with colleagues or clients on various Excel versions, it is good practice to document XLOOKUP usage or create fallback versions of critical lookup formulas using INDEX/MATCH for maximum compatibility across the organization's diverse Excel environment.