Excel 2010/2013
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 

< 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: Excel 2010 Expert Level 3
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
 
 

Student Interaction: Excel 2010 Expert 4

Richard on 5/24/2011:  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 - Lots More!
 Sarita on 10/14/2011: Very, very helpful. I like the pace, the tips, and the examples. I also like the list comparison feature as that minimizes my need to loop in Access. I'm also using Mac Excel 08 (Windows Excel to be used at my new job) and most of the functions translated with the exception of Insert Table (insert List on Mac) and naming a Table Range, which isn't an option on the Mac. I'm not sure which module the drop-down list is covered in (I bought 4,6,7) as I'd like to know how to calculate functions based on the chosen list value (as demo'd in the Match & Index section of this module where users can choose courses from a class list. On my spreadsheet, I was able to produce my drop-down course list but the match function refers to a static course row.). Overall, I was able to follow along and create examples as you taught, pausing where needed. Great job!
Lynn Staszak on 2/15/2013: I finally understand the MATCH and INDEX functions! I've never found an example to explain them as well as this training does. Thanks, Richard!
Benjamin Chua on 4/12/2013: time 1:18 expert level 4 - where can you find a solution if there is a drop down box in one of the colums? by increasing the rows it is not giving me a drop down box.

Reply from Richard Rost:

Ben, VLOOKUP will use whatever value is in that field. If it's an index for a list, then that's the value you'll get.

Hong Yuan on 7/18/2013: For Excel 2010 Expert class 7:

what if we have two people have the exact guesses? I tried it but Excel only matched the first winner's position and missed the second winner who guessed the same.

Desiree

Reply from Richard Rost:

Desiree, I'm sorry but you're going to have to give me a little context here... it's been over 2 years since I recorded that video, and without re-watching it, I don't know to what example you're referring. Please expand on your question.

al johnson on 8/23/2013: I have a situation where I need to a number rating 8-1, depending on if you are married, divorced or single and your age less than 21, more than 21, and more than 35. How do you state that in a cell using a vlookup, lookup or reference?

Reply from Richard Rost:

I'd need to know more about how your sheet is set up, how the criteria are assigned, etc.

al j on 8/26/2013: What I am trying to do is create a dataase using Excel. A person keys in M for married, <2 years, then that person point will be 8, <5, then that person point will be 4, >5, then that person point will be 1. I just tried to use a lookup table but I have no clue to use less than equal to formula and lookup at the same time. Help!!!

Reply from Richard Rost:

Sounds like you need to use a combination of IF and VLOOKUP. Without seeing your sheet, I can't give you any more direction.

Anonymous on 4/25/2014: Hi Richard, for the LOOKUP function, does the "lookup_vector" have to be 1st column or row (of a table_array) or could it be any column or row (more like an INDEX function)?
 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP