Highline Excel 2016 Class 11

Lookup Functions & Formulas, Comprehensive Lessons, 20 Examples

DESCRIPTION

In this video learn about:
(00:12) Introduction
(00:51) Data Validation List. VLOOKUP, Exact Match. IF & ISBLANK functions. Goal: Create Invoice.
(09:43) HLOOKUP with Exact Match Lookup. Goal: Lookup Tyrone’s Phone Number. Horizontal Lookup is rare.
(10:48) VLOOKUP with Approximate Match Lookup. Goal: Lookup Pipe size.
(13:44) VLOOKUP with Approximate Match Lookup. Goal: Lookup Commission Paid.
(14:38) VLOOKUP with Approximate Match Lookup. Cell Reference for Column Index Number. Goal: Get Rating and Commission.
(16:20) VLOOKUP and MATCH functions with Exact Match Lookup. MATCH for Column Index Number. Goal: Retrieve employee data.
(22:55) VLOOKUP & ROWS or COLUMNS with Exact Match Lookup. ROWS or COLUMNS for Column Index Number. Goal: Retrieve record from Employee Table.
(26:53) Multiple VLOOKUP functions. Goal: Calculate taxes from complex tax table.
(33:16) How to use VLOOKUP, LEFT and SEARCH to do a “Partial Text Lookup. Goal: Lookup Product Price.
(34:34) Look at Data Mismatch in lookup formulas.
(36:55) INDEX and MATCH functions to do a “Lookup Left”. INDEX array argument is a column. Goal: Given a flight range, lookup boomerang name in first column.
(39:51) INDEX and MATCH to find Vendor for Low Bid. INDEX array argument is a row. Goal: lookup vendor name for lowest bid.
(42:52) INDEX and MATCH functions to do a Two-Way Lookup. INDEX array argument is a table. Goal: Do Two-Way Lookup to find Discount.
(47:00) INDEX and MATCH to lookup a whole column or row. INDEX array argument is a table. Goal: Lookup: 1) whole column of values then 2) whole row of values.
(51:23) How to use VLOOKUP & CHOOSE to do a lookup to multiple tables. Goal: Lookup Commission rate from Multiple Lookup tables.
(57:25) LOOKUP function with lookup_value and array arguments. Approximate Match. Goal: Lookup Commission and Rating. LOOKUP to enter fewer arguments.
(01:00:55) LOOKUP with lookup_value and lookup_vector and result_vector arguments. Approximate Match. Goal: Lookup Last Date and find site. LOOKUP to do Array Operation.
(01:05:08) LOOKUP with lookup_value and lookup_vector and result_vector arguments. Approximate Match. Goal: “Lookup Adding” to total costs. LOOKUP to do Array Operation.
(01:09:28) INDEX and ROWS to Flip Grade Table. Goal: Flip a Grade Table.
(01:13:20) Use MATCH to Compare Two Lists. Goal: Is an item in List 1 also in List 2?
(01:15:13) Use MATCH to Compare Two Lists. Goal: Is an item in List 1 NOT in List 2?
(01:16:40) Summary

WORKBOOKS

Download “Highline Excel 2016 Class 11.zip” Highline-Excel-2016-Class-11.zip – Downloaded 23 times – 1 MB







Be the first to comment

Leave a Reply