Excel Agility - VLOOKUP and Better Alternatives
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Software, Taxation and Accounting, Finance
- Credit: ATAAA 1.0, ATATX 1.0, ATAOP 1.0, CPE 2.0
-
This course is designed to help you improve the integrity and resilience of your spreadsheets. In part 2 of Lookup Functions, Excel expert David Ringstrom, CPA, delves deeper into lookup functions and shares multiple troubleshooting techniques that will enable you to work more efficiently in Excel. David’s presentation begins with an overview of the popular VLOOKUP function, contrasting it with the HLOOKUP and VLOOKUP functions. You’ll also learn how to take lookup functions further with the Data Validation and Table features and how to implement next-level lookup functions, such as MATCH/INDEX, CHOOSE, SUMIF, and SUMIFS.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 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.
Who Would Be Interested in This Course:
Practitioners who would benefit from learning more about Excel’s lookup functions in order to work more efficiently.
Your Benefits of Attending:
- Using VLOOKUP to perform approximate matches.
- Removing the Table feature from a worksheet if it’s no longer needed.
- Comparing HLOOKUP to VLOOKUP for performing horizontal matches versus vertical matches.
- Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
- Learning what types of user actions can trigger #REF! errors.
- Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces.
- Eliminating inputs that could cause VLOOKUP to return #N/A with Data Validation.
- Identifying situations where VLOOKUP may return #N/A instead of a value.
- Using the TEXT function to force lookup values to match text-based table arrays.
- Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
- Using the SUMIFS function to sum values based on multiple criteria.
Learning Objectives:
- Identify what you can use in place of the word TRUE in VLOOKUP to return an approximate match.
- State what the MATCH function returns when the lookup_value is found.
- Recall how to execute horizontal matches with the HLOOKUP function.
Level:
Intermediate
Format:
On-Demand Webcast
Instructional Method:
Self-Study
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Experience Working with Excel Lookup Functions
Advance Preparation:
None
Table of Contents
Introduction
Excel Versions 00:00:48
Introduction to VLOOKUP 00:02:11
VLOOKUP - Approximate Match 00:08:24
Introduction to HLOOKUP up 00:13:45
Introduction to LOOKUP 00:17:21
Streamlining VLOOKUP 00: 20:51
Missing Data Triggers #N/A in VLOOKUP 00:26:05
VLOOKUP - Data Validation 00:27:27
VLOOKUP with IFERROR 00:33:00
VLOOKUP #REF! Error 00:37:36
VLOOKUP with INFA 00:41:01
VLOOKUP - Texts vs Numbers 00:44:49
VLOOKUP - Texts vs Numbers (cont.) 00:48:10
VLOOKUP with Wild-Card Criteria 00:49:24
Introduction to the MATCH Function 00:52:29
VLOOKUP with MATCH 00:54:17
Using the Table Feature with VLOOKUP 00:57:28
Undoing the Table Feature 01:02:21
VLOOKUP Futureproofed 01:04:03
MATCH / INDEX 01:05:37
INDEX with Two MATCH Functions 01:09:31
Introduction to the CHOOSE Function 01:14:52
VLOOKUP with CHOOSE Function 01:19:34
SUMIF Function 01:24:09
Introduction to SUMIFS 01:27:56
SUMIFS Function with One Range Criteria 01:32:42
SUMIFS Function with Two Range Criteria 01:34:51
VLOOKUP - Texts vs Numbers 01:37:11
Conclusion 01:41:08:08
Index
- #N/A 00:26:10
- #REF! Error 00:37:46
- CHOOSE Function 01:14:54
- Data Validation 00:27:29
- HLOOKUP 00:13:49
- IFERROR 00:33:24
- INDEX Function 01:06:24
- INFA Function 00:41:30
- ISNUMBER Function 00:46:11
- LOOKUP 00:17:43
- MATCH Function 00:52:57
- SUMIF Function 01:24:13
- SUMIFS 01:28:01
- Table Feature 00:58:02
- VLOOKUP 00:02:13
- Wildcards 00:49:31
#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.
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.
Data Validation : An Excel feature that allows users to assign data entry rules to one or more cells within an Excel worksheet.
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.
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.
ISNUMBER : Use the ISNUMBER function to check if a value is a number. ISNUMBER will return TRUE when value is numeric and FALSE when not.
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 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.
