Download workbook: people.highline.edu/mgirvin/AllClasses/214_2013/214/Busn214_2013.htm
Topics in this video:
1. Why do we have to know lookup functions? (00:38 min)
2. VLOOKUP, Exact Match (03:44 min). VLOOKUP delivers a value to the cell.
3. What happens if lookup value is not in your table? (08:34 min)
4. What happens if there are duplicates in first column of lookup table? (08:57 min)
5. VLOOKUP and IFERROR functions (10:38 min)
6. VLOOKUP ISNA and IF functions solution for Excel 2003 or earlier (12:22 min)
7. VLOOKUP and IFNA functions (13:19 min)
8. VLOOKUP and Data Validation List (14:37 min)
9. VLOOKUP with Named Range (17:23 min)
10. VLOOKUP as Formula Element (18:46 min)
11. The F2 Key for toggling between the 3 Formula Edit Modes: Edit, Enter, Point (19:42 min)
Edit = Arrow Keys move through formula
Enter = Arrow Keys can put cell references in formula
Point = Arrow Keys are putting cell references in formula (dancing ants are dancing).
12. (Formula Evaluator (Evaluate Formula) to see VLOOKUP in Action (20:48 min)
13. Creating an Invoice: Data Validation List, VLOOKUP & IFNA, ROUND & IFERROR (21:30 min)
14. HLOOKUP function (26:33 min)
15. The Hows and Whys of Approximate Match Compared to Exact Match (27:36 min)
16. VLOOKUP, Approximate Match: Commissions Example (30:13 min)
17. VLOOKUP, Approximate Match: Grading Example (33:10 min)
18. DO NOT GET TRICKED BY NUMBER FORMATTING!!!! VLOOKUP and Percentages!! (35:05 min)
19. VLOOKUP, Approximate Match: Tax Example (36:23 min)
20. Challenge for you!!! (40:31 min)
For answer to challenge see 01:00:18 minute mark of this video:
This is the Highline Community College Class, Spreadsheet Construction taught by Michael Girvin: Basic To Advanced Excel. It contains 50 video lectures teaching the basics of Excel, as well as advanced concepts.
Be sure to download the workbook to follow along with the examples. They are found on the official course website.