Excel 2010-2019 Excel 2007 Excel 2003 Tips & Tricks Excel Forum Course Index CIG Excel Book

 Home   News   Courses   Seminars   Templates   Help   TechHelp   Forums   Order   Contact   Logon More... What's New? Tips & Tricks Access Index Access Tips Excel Index Excel Tips Waiting List

 < Previous: Excel Expert 3 Next: Excel Expert 5 >

Excel 2010 Expert Level 4
Microsoft Excel Expert Tutorial - 1 Hour, 10 Minutes

This is the fourth class in our Excel 2010 Expert Series. It picks up where Expert Level 3 left off. The focus of this course is working with lookup functions in Excel. Topics covered include:

 - Using VLOOKUP to get a value from a list  - Assiging a letter grade based on final average  - Understand range lookups vs. exact matches  - Lookup an employee's pay rate, calculate pay  - Use INDEX and MATCH for enhanced lookups  - Create cell references: ADDRESS, INDIRECT  - Calculate year-to-date sales  - Compare two lists for missing items  - Find the value in a list closest to a target value Order Now

If you would like a preview of what's covered in this class,
click here to watch the first lesson of this course (free of charge), or scroll down for more information.

Excel 2010 Expert Level 4
 Description: Excel 2010 Expert Level 4 Versions: Microsoft Office Excel 2010 Pre-Requisites: Running Time: 1 Hour, 10 Minutes Cost: \$19.99

This course is for the expert user who has good experience with Microsoft Excel or has completed the five courses in our Beginner Series plus Expert Level 1, 2, and 3 classes. This course will teach you how to work with lookup functions in Excel.

We will begin by learning about VLOOKUP which is one of the most popular, and versatile lookup functions. You will see how to take any date, figure out what day of the week it falls on, convert that to a number (1-7), and then look that number up in a table to display the full text of the weekday name, like "Wednesday."

Next you'll learn how to perform a range match with VLOOKUP. This will let you find a value that's less than or equal to your lookup value. Now you can take a student's final average and look up a letter grade.

In the next lesson, we'll build an employee time sheet. You'll enter in the employee's name, start time, end time, and the number of hours he worked. We'll again use VLOOKUP but this time we'll perform an exact match to find the employee in a separate worksheet and return his pay rate. Now we can calculate his total pay for the day based on that and the number of hours he worked.

Next we'll learn about some functions that are related to VLOOKUP, including HLOOKUP and LOOKUP. We'll then learn about MATCH and INDEX which are very powerful functions when used together. They can do things that VLOOKUP can't do. For example, we'll create a chart that shows quantity discounts for students in training classes. You'll then be able to enter the class name (Excel 2) and the number of students (7) and calculate the price per student using the MATCH and INDEX functions. You can also use MATCH and INDEX to look up values to the LEFT side of your lookup column - which VLOOKUP can't do.

Next you will learn how to use the ADDRESS and INDIRECT functions to construct and deconstruct cell references. For example, you'll be able to figure out the sales between two dates (or even year to date sales) by having the user enter in the start and end dates. This will allow you to construct cell ranges like B2:B10 on the fly in your formulas.

We will see a couple of examples of how to put all of these lookup functions to good use. In the first example, you'll be given two lists of names. You'll learn how to compare them to figure out if any names from list one are missing from list two (and vice versa).

Finally, you'll be given a list of values and a target number. You'll learn how to figure out which value is closest to the target number. For example, you've got a bunch of people trying to guess how many gumballs are in a bowl. You've got all of their guesses, now how do you figure out which guess is closest to the correct value? We'll use a combination of functions, including MATCH and INDEX to figure this one out.

Again, this is the perfect class for anyone who wants to learn how to work effectively with lookup functions including the VLOOKUP function in Microsoft Excel 2010. Mastering VLOOKUP will definitely take your spreadsheets to the next level. Of course, if you have any questions about whether or not this class is for you, please contact me.

Complete Outline - Excel 2010 Expert Level 4

 00. Intro (5:58) 01. VLOOKUP 1 (8:10) Look up week day name VLOOKUP simple function Lookup Value Table Array Column Index Number Problem With AutoFill Use Absolute Reference Use Named Range 02. VLOOKUP 2 (7:01) Student Letter Grades Range Lookup Assign A to F Convert to Table Hide empty values IF and ISBLANK 03. VLOOKUP 3 (9:22) Employee Time Sheet Employee List Table Rename a Table Timecard Worksheet Exact Match Lookup Lookup Employee Name Lookup Pay Rate Calculate Hours Worked Calculate Total Pay 04. Other Lookups (13:50) HLOOKUP LOOKUP MATCH, INDEX Less Than Exact Match Greater Than Wildcard Match Backward Lookup Searching an Array 05. Cell References (10:26) Construct a cell reference Deconstruct a cell reference ADDRESS INDIRECT Year to date sales figure Sales between two months 06. Compare Two Lists (4:18) Use MATCH to compare Is item in list missing ISNA 07. Closest to Value (7:06) Which value is closest to target Calculate Difference Autofill Double-Click Trick ABS Absolute Value MIN to find closest value MATCH to locate the value INDEX to determine winner Closest to average value

Keywords: microsoft excel tutorial, microsoft excel 2010 tutorial, microsoft office excel 2010 tutorial, microsoft excel 2010 training, VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, ADDRESS, INDIRECT, ISNA, Lookup Table, Array, Student Letter Grades, Range Lookup, Exact Lookup, IF, ISBLANK, Time sheet, timecard, exact match, cell reference, compare two lists, closest value

 You may want to read these articles from the 599CD News:
 8/18/2020 Excel Expert 11 Lessons 8/17/2020 Excel Courses 8/17/2020 Excel Expert Courses 8/15/2020 Excel 2010 Beginner 1 8/14/2020 Excel XLOOKUP Function 8/14/2020 Excel Worksheet Change VBA 8/14/2020 Excel TechHelp 7/27/2020 How to Get Microsoft Excel for Free 5/25/2020 Excel Expert 11 5/20/2020 Excel My Name is Richard

 Learn  Access - index Excel - index Word - index Windows - index PowerPoint - index Photoshop - index Visual Basic - index ASP - index Seminars More... Customers  Account Login Online Theater Downloads Lost Password Free Upgrades Insider Circle Student Databases Change Email Info  Latest News New Releases User Forums Topic Glossary Tips & Tricks Articles Search The Site Waiting List Production Schedule Collapse Menus Help  Live Chat Customer Support WalkThru Tutorials Troubleshooting FAQs TechHelp Consulting Services About  Background Testimonials Jobs Affiliate Program Richard Rost Free Lessons Mailing List Order  Video Tutorials Handbooks MYOLP Memberships Learning Connection Idiot's Guide to Excel Volume Discounts Payment Info Shipping Terms of Sale Contact  Live Chat General Info Support Policy Contact Form Email Richard Mailing Address Phone Number Fax Number Course Survey