1. Aurora Training Advantage
  2. Live Webinar
  3. Mastering Lookup Formulas and High Powered Alternative Techniques

Live Webinar

Mastering Lookup Formulas and High Powered Alternative Techniques

Webinar Details $219

  • Webinar Date: May 31, 2023
  • Webinar Time: 2:00pm - 3:40pm EDT   live
  • Webinar Length: 100 Minutes
  • Guest Speaker:   David Ringstrom
  • Topic:   Business Administration, Business Skills, Software, Taxation and Accounting, Human Resources
  • Credit:   CPE 2.0, ATATX 1.5, ATAAA 1.5, ATAOP 1.5

All Access Membership

Time-saving lookup functions allow you to quickly find the specific information you’re looking for, such as pay rates, item prices, accounting results, etc., thereby eliminating the need to manually link to specific cells. In this presentation, Excel expert David Ringstrom, CPA, introduces several lookup functions, including VLOOKUP, HLOOKUP, MATCH, and CHOOSE, and provides context on when you’re likely use them. He demonstrates troubleshooting techniques and prepares you to deal with subtle issues that can prevent lookup functions from working properly. 

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016 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 2021, Excel 2019, and so on.


Who should attend:

Practitioners who can benefit by using lookup functions, such as VLOOKUP and HLOOKUP, in order to work more efficiently in Excel.

Topics typically covered:

  • Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
  • Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces.
  • Discovering how to use wildcards and multiple criteria within lookup formulas.
  • Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts.
  • Displaying alternate results with XLOOKUP by populating the If_Not_Found argument instead of using IFERROR or IFNA.
  • Distinguishing how wildcards work with Excel's XLOOKUP function
  • Eliminating inputs that could cause VLOOKUP to return #N/A with Data Validation.
  • Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
  • Exploring the XLOOKUP worksheet function in Excel 2021 and Microsoft 365.
  • Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
  • Identifying situations where VLOOKUP may return #N/A instead of a value.
  • Investigating the risks associated with the obsolete LOOKUP function.

Learning objectives:

  • Apply the VLOOKUP and HLOOKUP functions.
  • Define what user actions can trigger #REF! errors.
  • Recall how to deal with subtle issues that can prevent lookup formulas from working properly.
  • Recall how to use the MATCH worksheet function to identify differences between two lists.

Level: Basic

Format: Live webcast

Instructional Method: Group: Internet-based

NASBA Field of Study:

Computer Software & Applications (2 hours)

Program Prerequisites: None

Advance Preparation: None

  1. Introduction
  2. Please Ask Questions Today 00:02:04
  3. Excel Versions 00:04:06
  4. VLOOKUP Introduction 00:05:27
  5. VLOOKUP - Approximate Match 00:11:01
  6. HLOOKUP Introduction 00:15:23
  7. LOOKUP Introduction 00:19:07
  8. VLOOKUP Blank Cells Trigger #N/A 00:24:04
  9. VLOOKUP - Data Validation 00:27:13
  10. VLOOKUP Deleting Columns Causes #REF 00:32:19
  11. VLOOKUP with IFNA (Excel 2013+) 00:37:27
  12. VLOOKUP Text vs. Numbers Cause #N/A 00:42:04
  13. VLOOKUP Resolving Text vs. Numbers 00:46:07
  14. VLOOKUP with Wildcard Criteria 00:46:18
  15. MATCH Introduction 00:52:20
  16. VLOOKUP with MATCH 00:54:39
  17. VLOOKUP with Table Feature 00:58:24
  18. Undoing the Table Feature 01:03:33
  19. INDEX/MATCH Introduction 01:05:05
  20. XLOOKUP Introduction (Microsoft 365) 01:12:19
  21. XLOOKUP If_Not_Found Argument 01:15:55
  22. XLOOKUP Can Return Multiple Columns 01:18:17
  23. XLOOKUP Finding the Last Match 01:25:01
  24. XLOOKUP Approximate Matches 01:28:45
  25. XLOOKUP Matching Multiple Criteria 01:31:10
  26. SUMIF Introduction 01:34:52
  27. SUMIFS Function with One Range Criteria 01:36:39
  28. SUMIFS with Two Range Criteria 01:38:02
  29. SUMPRODUCT as SUMIFS Alternatives 01:39:45
  30. Thank You for Attending! 01:42:25
  31. Presentation Closing 01:42:28
  • #N/A Error 00:21:50, 00:24:12, 00:32:15, 00:49:56
  • #REF! Error 00:32:34
  • AVERAGE 00:05:47
  • Data Validation 00:27:21
  • Dynamic Array Function 01:18:25
  • Formula 00:34:30
  • FORMULATEXT 01:13:04
  • HLOOKUP 00:15:29
  • IFERROR 01:15:57
  • IFNA Function 00:37:36, 01:16:00
  • INDEX Function 01:05:15
  • ISNUMBER 00:43:52
  • LEN Function 00:43:32
  • LOOKUP 00:19:07, 00:52:30
  • MATCH Function 00:52:25, 01:05:09
  • SUM 00:05:41
  • SUMIF 01:34:52
  • SUMIFS 01:36:40
  • SUMPRODUCT 01:39:49
  • Table 00:59:12, 01:03:45
  • Table Array 00:06:23, 00:12:44
  • Table Feature 00:58:52
  • Text to Columns Wizard 00:46:21
  • VLOOKUP 00:01:24, 00:05:31, 00:07:37, 00:38:02, 00:46:48, 01:15:56
  • Wildcards 00:46:18
  • XLOOKUP 00:04:15, 00:19:30, 01:18:17

#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.

AVERAGE : Returns the average (arithmetic mean) of the arguments.

Dynamic Array Function: Dynamic Arrays will make certain formulas much easier to write. You can now filter matching data, sort, and extract unique values easily with formulas. Dynamic Array formulas can be chained (nested) to do things like filter and sort. Formulas that return more than one value will automatically spill.

FORMULATEXT: The Excel FORMULATEXT function returns a formula as a text string from given reference. You can use FORMULATEXT to extract a formula as text from a cell. If you use FORMULATEXT on a cell that doesn't contain a formula, you'll get an #N/A error.

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.

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.

LEN Function: The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.

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.

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.

SUMPRODUCT: The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.

Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.

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.

Guest Speaker

  • David H. Ringstrom, CPA

    David H. Ringstrom, CPA

    David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David’s mantra is “E [...]

    View Full Profile