Mastering Lookup Formulas and High Powered Alternative Techniques

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, 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 “Either you work Excel, or it works you,” so he focuses on what he sees use [...]

  View Full Profile

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

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

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.

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.

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.

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.

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.

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.

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.

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

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.