On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Software, Taxation and Accounting, Human Resources
- Credit: ATAAA 1.5, ATAOP 1.5, ATATX 1.5, CPE 2.0
-
Excel expert David H. Ringstrom’s mantra is “Either you work Excel, or Excel works you.” You can avoid the latter and become a master of lookup formulas after participating in this broad-reaching webinar. David offers helpful tweaks you can use with the venerable VLOOKUP function. In addition, he discusses alternatives to VLOOKUP, including INDEX and MATCH, SUMIF, SUMIFS, SUMPRODUCT, IFNA, and OFFSET functions.
Lookup formulas are far superior to manually pointing to specific data elements in a spreadsheet. Many users rely on VLOOKUP to return data from other locations in a worksheet, but is that the most efficient approach? David teaches you how to perform wildcard lookups, where only partial criteria are provided, as well as how to execute multiple-criteria lookups.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 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.
Microsoft 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.
Who should attend:
Practitioners who may benefit from working more efficiently in Excel by utilizing lookup formulas.
Topics typically covered:
• Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
• Transforming numbers stored as text into values by way of the Text to Columns wizard.
• Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
• Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces.
• Learning what types of user actions can trigger #REF! errors.
• Understanding how the VLOOKUP function allows you to look up data instead of having to manually reference individual cells.
• Using XLOOKUP to search lists from the bottom up to find the last match (instead of only the first match with VLOOKUP).
• Distinguishing how wildcards work with Excel's XLOOKUP function
• Using the MATCH function to find the position of an item in a list.
• Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts.
• Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
• Using the SUMIFS function to sum values based on multiple criteria.
Learning objectives:
• Identify the limitations of VLOOKUP and learn about alternative functions.
• Recall how to future-proof VLOOKUP by using Excel’s Table feature versus referencing static ranges.
• Define how to improve the integrity of your spreadsheets with Excel’s VLOOKUP function.
- Introduction
- Please Ask Questions Today 00:03:02
- Excel Versions 00:04:36
- Addresses 00:06:00
- Function Wizard 00:11:20
- Lookup_value 00:16:16
- Table_array 00:18:26
- Column_index_num 00:23:01
- Range_lookup 00:24:20
- Address - Formula 00:28:51
- #N/A Error With VLOOKUP 00:33:49
- Overcoming Unwanted AutoCorrections 00:36:35
- Extraneous Spaces Triggers #N/A in VLOOKUP 00:40:01
- Future-Proofing VLOOKUP 00:47:46
- Using The Table Feature With VLOOKUP 01:06:57
- Undoing The Table Feature 01:11:06
- IFNA Function With VLOOKUP 01:13:01
- Other Types of VLOOKUP Errors 01:15:21
- Concatenate City, State, and Zip 01:19:09
- City, State Zip - Formula 01:24:09
- Viewing Two Worksheets at Once - Steps 1-8 01:30:59
- Viewing Two Worksheets at Once - Steps 9-13 01:33:18
- Look-Up Data From a Second Workbook 01:33:25
- Item ID Look-Up 01:34:07
- Perfecting the Item ID Lookup 01:34:08
- Price Look-Up 01:39:59
- VLOOKUP Approximate Matches 01:40:01
- VLOOKUP as Alternative to IF 01:40:23
- Tax Rates 01:40:24
- HLOOKUP Introduction 01:40:26
- XLOOKUP Introduction (Excel 2021+) 01:41:09
- SUMIF Introduction 01:37:09
- Thank You for Attending! 01:44:57
- Presentation Closing 01:45:16
- #N/A Error 00:33:55, 01:05:58
- #REF! Error 01:15:26
- Cell 00:11:52, 00:27:11, 00:48:32, 01:08:09, 01:35:10
- Column 00:17:29, 00:19:13, 00:20:58, 00:23:10, 01:15:31, 01:34:51, 01:40:33
- Concatenation 01:19:11
- Dialog Box 00:12:49, 00:27:01
- Formula 00:07:03, 00:11:28, 00:12:16, 00:28:38, 01:31:12, 01:34:18
- Formula Bar 00:12:25, 00:27:05
- Function Wizard 00:11:19, 00:28:59
- HLOOKUP 00:02:24, 00:07:41, 00:56:13, 01:40:29
- IFERROR 01:13:12
- IFNA Function 01:13:06
- Keyboard Shortcut 01:34:28
- LOOKUP 00:02:17, 00:06:14, 00:08:54, 00:17:14, 00:27:18, 00:40:08
- Microsoft 365 00:04:39, 01:41:16
- Ribbon 00:12:35
- SUMIF 00:02:24, 00:17:35, 00:56:20
- SUMIFS 00:56:21
- Table Array 00:18:23, 00:27:21, 00:29:09, 01:33:45
- Table Feature 00:48:12, 01:06:59
- VLOOKUP 00:01:25, 00:07:29, 00:12:06, 00:16:31, 00:18:38, 00:23:17, 00:28:19, 0047:48, 01:07:39, 01:15:51, 01:24:20
- Workbook 01:33:30
- Worksheet 01:31:08
- XLOOKUP 00:01:37, , 00:56:13, 01:41:12
#N/A Error: Excel displays this error when a lookup function, such as VLOOKUP or MATCH, cannot return the requested information.
#REF! Error: Excel displays this error when a formula contains an invalid cell reference. For instance, Excel’s VLOOKUP function may return #REF! if the col_index_num argument is incorrect. Other formulas may return #REF! if a user deletes one or more columns and Excel can’t adjust the cell references properly.
Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Concatenation: A technique that allows you to join two or more pieces of text together. Although its simplest to use the ampersand (&), you can also use the CONCATENATE function in Excel.
Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.
Formula: A formula is an expression which calculates the value of a cell.
Formula Bar: A toolbar at the top of the Microsoft Excel spreadsheet window that you can use to enter or copy an existing formula into cells or charts. It is labeled with function symbol (fx). By clicking the Formula Bar, or when you type an equal (=) symbol in a cell, the Formula Bar will activate.
Function Wizard: The function wizard opens all of the functions in Excel, through sub-menus and categories. To use the Function Wizard you can either choose Function from the Insert menu or you can click on the Function Wizard button "fx" located on the Standard toolbar.
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.
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.
Keyboard Shortcut: A keyboard shortcut is a series of one or several keys that invoke a software program to perform a preprogrammed action. This action may be part of the standard functionality of the operating system or application program, or it may have been written by the user in a scripting language.
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.
Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
Ribbon: The "ribbon" is the strip of buttons and icons located above the work area that was first introduced in Excel 2007. The ribbon replaces the menus and toolbars found in earlier versions of Excel. Above the ribbon are a number of tabs, such as Home, Insert, and Page Layout.
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.
Table Feature : The Table feature in Excel 2007 and later is an improvement on the List feature in Excel 2003 and earlier. The Table feature provides enhancements that make it much easier to analyze lists of data.
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.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
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.
