New XLOOKUP Function in Excel
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Taxation and Accounting, Software
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
Microsoft announced the new XLOOKUP function for Office 365 in August 2019. As is typical for Office 365 features, this function will be pushed out to users in waves. In this presentation you’ll get an overview of the new XLOOKUP function and see how it compares to both VLOOKUP, HLOOKUP and INDEX/MATCH, as well as the similarities that XLOOKUP has with the obsolete LOOKUP function in Excel.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the Office 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.
Office 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.
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 find lookups to the LEFT (instead of only to the right with VLOOKUP).
• Contrast how XLOOKUP defaults to an exact match instead of an approximate match like VLOOKUP.
• Return a cell range, instead of a single value, which means you can return multiple values.
• Understand wildcard support in XLOOKUP versus VLOOKUP.
• Find approximate matches without sorting.
• Contrast the MATCH function to the new XMATCH function.
• Learn about the Microsoft Excel Insider program that can help speed up access to forthcoming new features in Office 365.
Learning objectives:
•Identify the purpose of Excel’s XLOOKUP function.
•State the purpose of the column_index_num argument within VLOOKUP.
•Identify the number of criteria pairs that you can specify in the MAXIFS function.
•State what the MATCH function returns when the lookup_value is found.
- Introduction
- Please Ask Questions Today 00:02:31
- Excel Versions 00:04:17
- Office Insider Program 00:06:04
- MAC Office Insider Program 00:10:17
- VLOOKUP Introduction 00:10:47
- MATCH Introduction 00:17:15
- INDEX/MATCH Introduction 00:21:50
- XMATCH Introduction 00:26:27
- LOOKUP Introduction 00:31:01
- XLOOKUP Introduction (Office 365) 00:34:55
- Excel For MAC Function Screen Tips 00:38:47
- VLOOKUP with IFERROR 00:40:05
- XLOOKUP If_Not_Found Error Argument 00:44:18
- XLOOKUP Blank Cells Cause #N/A 00:47:37
- HLOOKUP Introduction 00:51:55
- XLOOKUP Lookup Horizontally 00:57:17
- XLOOKUP Approximate Matches 00:58:22
- XLOOKUP Wildcard Nuances 01:02:12
- VLOOKUP With CHOOSE Function 01:05:49
- XLOOKUP Looking To The Left 01:09:23
- XLOOKUP Finding The Last Match 01:11:01
- XLOOKUP Matching Multiple Criteria 01:13:57
- XLOOKUP Can Return Multiple Columns 01:19:25
- XLOOKUP Summing Multiple Columns 01:24:50
- XLOOKUP Text Vs. Number Causes #N/A 01:27:23
- Correcting Numbers Stored As Text 01:29:21
- XLOOKUP Duplicate Data Trap 01:30:49
- SUMIF Introduction 01:33:00
- SUMIFS Introduction 01:36:35
- XLOOKUP Summing Multiple Columns 01:39:46
- Thanks For Attending! 01:41:10
- Presentation Closing 01:41:37
- #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.
