Mastering Lookup Formulas and High Powered Alternative Techniques

Access this expert-led webinar instantly, available anytime on-demand.

Included in All-Access Membership
Live Webinar - no upcoming date
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.

Purchase Options

Select an attendee quantity to add to cart.

Recorded Webinar Only

$219.00
or

All Access Membership

The Aurora All Access Membership is designed to provide you with the training that you want when you want it. You will have 100% access to every live webinar, on demand webinar, professional alert, and podcast that Aurora Training Advantage offers with no additional cost.

Learn More About Our All Access Membership
$599.00
All Access Membership

Excel expert David H. Ringstrom’s mantra is “Either you work Excel, or Excel works you.” Avoid the latter and gain mastery over lookup formulas by attending this comprehensive webinar. David shares practical enhancements you can apply to the venerable VLOOKUP function while also exploring powerful alternatives, including INDEX and MATCH, SUMIF, SUMIFS, SUMPRODUCT, IFNA, OFFSET, and XLOOKUP.

Lookup formulas provide a far more efficient and reliable approach than manually referencing specific cells within a spreadsheet. While many users depend on VLOOKUP to retrieve data from other areas of a worksheet, David demonstrates when alternative methods may be more effective. You’ll learn how to perform wildcard lookups using partial criteria and how to build formulas that return results based on multiple criteria.

Every technique is demonstrated twice: first through step-by-step PowerPoint slides with numbered instructions, and then live within the subscription-based Microsoft 365 version of Excel. Throughout the presentation, David highlights differences users may encounter in earlier versions of Excel, including Excel 2019, 2016, 2013, and prior releases. Participants also receive detailed handouts and an Excel workbook containing many of the examples covered during the webcast.

Microsoft 365 is a subscription-based product that receives frequent feature updates, often monthly. In contrast, perpetual-license versions of Excel, such as Excel 2019 and Excel 2016, maintain fixed feature sets that do not change over time.

Your Benefits For Attending:
  • Identify the limitations of VLOOKUP and recognize alternative functions that may better fit specific lookup scenarios.
  • Recall how to future-proof VLOOKUP formulas by using Excel’s Table feature instead of static ranges.
  • Define techniques for improving spreadsheet integrity and accuracy through effective use of VLOOKUP and related lookup functions.

Who Should Attend:
Practitioners who want to work more efficiently in Excel by leveraging lookup formulas and related functions to improve accuracy, productivity, and spreadsheet integrity.

Topics Typically Covered:
  • Contrasting the INDEX and MATCH combination with VLOOKUP and HLOOKUP.
  • Transforming numbers stored as text into values using the Text to Columns wizard.
  • Employing the SUMIF function to total values associated with specified criteria.
  • Diagnosing #N/A errors caused by numbers stored as text or text containing extraneous spaces.
  • Learning what user actions can trigger #REF! errors.
  • Understanding how the VLOOKUP function enables data retrieval without manually referencing cells.
  • Using XLOOKUP to search lists from the bottom up and return the last matching result.
  • Distinguishing how wildcards work within Excel’s XLOOKUP function.
  • Using the MATCH function to determine the position of an item within a list.
  • Discovering the capabilities of the SUMPRODUCT function for payroll and other calculations.
  • Future-proofing VLOOKUP by using Excel Tables instead of static cell ranges.
  • Using the SUMIFS function to sum values based on multiple criteria.
Level: Intermediate
Format: Live Webcast
Instructional Method: QAS Self-Study (Traditional)
NASBA Field of Study: Computer Software & App (2 hours)
Program Prerequisites: None
Advance Preparation: No
  1. Introduction
  2. Please Ask Questions Today 00:03:02
  3. Excel Versions 00:04:36
  4. Addresses 00:06:00
  5. Function Wizard 00:11:20
  6. Lookup_value 00:16:16
  7. Table_array 00:18:26
  8. Column_index_num 00:23:01
  9. Range_lookup 00:24:20
  10. Address - Formula 00:28:51
  11. #N/A Error With VLOOKUP 00:33:49
  12. Overcoming Unwanted AutoCorrections 00:36:35
  13. Extraneous Spaces Triggers #N/A in VLOOKUP 00:40:01
  14. Future-Proofing VLOOKUP 00:47:46
  15. Using The Table Feature With VLOOKUP 01:06:57
  16. Undoing The Table Feature 01:11:06
  17. IFNA Function With VLOOKUP 01:13:01
  18. Other Types of VLOOKUP Errors 01:15:21
  19. Concatenate City, State, and Zip 01:19:09
  20. City, State Zip -  Formula 01:24:09
  21. Viewing Two Worksheets at Once - Steps 1-8 01:30:59
  22. Viewing Two Worksheets at Once - Steps 9-13 01:33:18
  23. Look-Up Data From a Second Workbook 01:33:25
  24. Item ID Look-Up 01:34:07
  25. Perfecting the Item ID Lookup 01:34:08
  26. Price Look-Up 01:39:59
  27. VLOOKUP Approximate Matches 01:40:01
  28. VLOOKUP as Alternative to IF 01:40:23
  29. Tax Rates 01:40:24
  30. HLOOKUP Introduction 01:40:26
  31. XLOOKUP Introduction (Excel 2021+) 01:41:09
  32. SUMIF Introduction 01:37:09
  33. Thank You for Attending! 01:44:57
  34. Presentation Closing 01:45:16

  • David H. Ringstrom, CPA

    David H. Ringstrom, CPA

    David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in E [...]

    View Full Profile

ATAAA Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in administrative.

ATAOP Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.

ATATX Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or [email protected].

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.

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

Customer Satisfaction Guarantee
Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.

Frequently Asked Questions

VLOOKUP is one of Excel's most widely used functions, but it has several well-known limitations that create errors and inefficiencies in real-world spreadsheets. Most critically, VLOOKUP can only search in the leftmost column of the table array and return values to the right—it cannot look left. It uses a positional column index number (col_index_num) that breaks silently when columns are inserted or deleted, causing wrong values to be returned without error messages. VLOOKUP also requires the entire table array to be referenced, creating performance inefficiencies in large datasets. The INDEX-MATCH combination overcomes all of these limitations: INDEX returns a value from any position in a range, while MATCH finds the position of the lookup value—together they perform lookups in any direction, on any column. They are unaffected by column insertions and reference only the necessary columns. Excel expert David Ringstrom's Mastering Lookup Formulas webinar, available through Aurora Training Advantage, provides detailed instruction on transitioning from VLOOKUP to INDEX-MATCH, along with wildcard and multi-criteria lookup techniques.
XLOOKUP is Microsoft's modern replacement for VLOOKUP, introduced in Excel 2021 and Microsoft 365. It addresses every major VLOOKUP limitation while adding powerful new capabilities. Unlike VLOOKUP, XLOOKUP can search in any direction—left, right, up, or down—and automatically handles the 'look left' scenario that VLOOKUP cannot. It accepts separate lookup and return arrays (rather than a combined table array with a column index), making formulas far more resilient to structural changes in the spreadsheet. XLOOKUP includes a built-in if-not-found argument that replaces the need for wrapping in IFERROR, and offers multiple match modes including exact match, approximate match, and wildcard matching. It can also search from the bottom up to find the last match—a capability VLOOKUP entirely lacks. For users on Excel 2021 or Microsoft 365, XLOOKUP should generally replace VLOOKUP in all new work. David Ringstrom's Mastering Lookup Formulas webinar, through Aurora Training Advantage, demonstrates XLOOKUP alongside its predecessor functions and explains when each approach is most appropriate.
SUMIF and SUMIFS serve as lookup alternatives in scenarios where you need to aggregate rather than retrieve individual records—making them particularly powerful for financial analysis and reporting. While VLOOKUP returns the first match for a lookup value, SUMIF will sum all matching values across an entire column, handling multiple instances of the same criteria elegantly (returning zero instead of #N/A when no match is found). This makes SUMIF ideal for situations like consolidating invoice totals by vendor, summing transactions by account code, or aggregating sales by product across thousands of rows. SUMIFS extends this capability to multiple simultaneous criteria—summing only the records where all specified conditions are met. Combined with the SUMPRODUCT function (which can calculate payroll amounts, weighted averages, and multi-condition sums without Ctrl+Shift+Enter), these functions address many analytical needs that practitioners incorrectly try to solve with VLOOKUP. David Ringstrom's Mastering Lookup Formulas webinar demonstrates each function with practical examples applicable to accounting, payroll, and financial reporting workflows.
#N/A errors in VLOOKUP are among the most frustrating and frequently misunderstood issues Excel practitioners encounter—and they almost always have one of a small number of root causes. The most common is a data type mismatch: the lookup value is stored as a number while the lookup column contains text representations of numbers (or vice versa). This often happens with account codes, ZIP codes, or IDs that look like numbers but are stored as text, or when VLOOKUP is used to match numeric values against data imported from external systems. The Text to Columns wizard can convert text-stored numbers to values. Extraneous leading or trailing spaces in lookup values or lookup columns are another frequent cause—the TRIM function removes these. Wildcard characters (* and ?) can be used in approximate text lookups when exact matching fails due to slight variations. The IFNA function (preferable to IFERROR in most cases) allows practitioners to display a custom result when a lookup returns #N/A without masking other error types. David Ringstrom's Mastering Lookup Formulas webinar provides a systematic troubleshooting approach for every common #N/A scenario in VLOOKUP.
One of VLOOKUP's most dangerous failure modes is its use of static range references (like $A$1:$D$100) for the table array. When new rows are added below row 100, they fall outside the formula's range and are silently excluded from lookups—producing wrong results without any error indicator. Converting the data range to an Excel Table (Ctrl+T) solves this problem automatically: Table references expand dynamically as new rows are added, so the VLOOKUP formula always covers the entire dataset. Additionally, Excel Tables use structured references (column names like Table1[Account Code]) instead of cell addresses, making formulas more readable and immune to breakage when columns are inserted. Using the Table feature with VLOOKUP creates formulas that are self-maintaining—accountants and analysts who work with growing datasets no longer need to manually update range references after each data import. This is one of the most impactful 'future-proofing' techniques Excel expert David Ringstrom teaches in his Mastering Lookup Formulas webinar, available through Aurora Training Advantage, which also covers INDEX-MATCH, XLOOKUP, SUMIFS, and wildcard lookup techniques.