Mastering Lookup Formulas and High Powered Alternative Techniques
Webinar Details $219
- Rated:
- 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:02:21
- Excel Versions 00:04:22
- VLOOKUP 00:05:10
- VLOOKUP - Approximate Match 00:11:22
- HLOOKUP Introduction 00:15:28
- LOOKUP Introduction 00:21:45
- VLOOKUP Blank Cells Trigger #N/A 00:25:47
- VLOOKUP - Data Validation 00:29:54
- VLOOKUP with IFERROR 00:33:31
- VLOOKUP Deleting Columns Causes #REF 00:38:29
- VLOOKUP with IFNA (Excel 2013+) 00:42:11
- VLOOKUP Text vs. Numbers Cause #N/A 00:48:05
- VLOOKUP Resolving Text vs. Numbers 00:51:57
- VLOOKUP with Wildcard Criteria 00:53:22
- MATCH Introduction 00:57:09
- VLOOKUP with MATCH 01:00:32
- VLOOKUP with Table Feature 01:04:08
- Undoing the Table Feature 01:08:09
- VLOOKUP Futureproofed 01:10:01
- INDEX/MATCH Introduction 01:10:01
- XLOOKUP Introduction (Microsoft 365) 01:15:26
- XLOOKUP If_Not_Found Argument 01:19:48
- XLOOKUP Can Return Multiple Columns 01:21:54
- XLOOKUP Finding the Last Match 01:26:17
- XLOOKUP Approximate Matches 01:28:56
- XLOOKUP Matching Multiple Criteria 01:32:17
- SUMIF Introduction 01:35:53
- SUMIFS Function with One Range Criteria 01:37:18
- SUMIFS with Two Range Criteria 01:38:33
- SUMPRODUCT as SUMIFS Alternatives 01:39:06
- Thank You for Attending! 01:41:04
- Presentation Closing 01:41:39
- #N/A Error 00:25:52
- #REF! Error 00:38:42
- Concatenation 00:56:22
- Data Validation 00:29:55
- Direct References 00:05:30
- HLOOKUP 00:15:29
- IFERROR 00:26:38
- IFNA Function 00:42:43
- INDEX 00:57:20
- LET Function 00:37:46, 01:15:53
- LOOKUP 00:22:01, 01:04:29
- MATCH Function 00:57:12, 01:24:23
- SUMIF 01:36:00
- SUMIFS 01:36:00, 01:37:24
- Table Array 00:06:50, 00:11:44
- Table Feature 01:04:08
- Text to Columns Wizard 00:52:11
- VLOOKUP 00:05:13
- Wildcards 00:53:36
- XLOOKUP 00:23:27, 01:17:44
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.
LET Function: The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax.
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.
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.
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.
