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