Loading...

Highline Excel 2016 Class 11: Lookup Functions & Formulas, Comprehensive Lessons, 20 Examples

52,822 views

Loading...

Loading...

Transcript

The interactive transcript could not be loaded.

Loading...

Rating is available when the video has been rented.
This feature is not available right now. Please try again later.
Published on May 13, 2016

Download Files: https://people.highline.edu/mgirvin/A...
In this video learn about:
(00:12) Introduction
1. (00:51) Data Validation List. VLOOKUP, Exact Match. IF & ISBLANK functions. Goal: Create Invoice.
2. (09:43) HLOOKUP with Exact Match Lookup. Goal: Lookup Tyrone's Phone Number. Horizontal Lookup is rare.
3. (10:48) VLOOKUP with Approximate Match Lookup. Goal: Lookup Pipe size.
4. (13:44) VLOOKUP with Approximate Match Lookup. Goal: Lookup Commission Paid.
5. (14:38) VLOOKUP with Approximate Match Lookup. Cell Reference for Column Index Number. Goal: Get Rating and Commission.
6. (16:20) VLOOKUP and MATCH functions with Exact Match Lookup. MATCH for Column Index Number. Goal: Retrieve employee data.
7. (22:55) VLOOKUP & ROWS or COLUMNS with Exact Match Lookup. ROWS or COLUMNS for Column Index Number. Goal: Retrieve record from Employee Table.
8. (26:53) Multiple VLOOKUP functions. Goal: Calculate taxes from complex tax table.
9. (33:16) How to use VLOOKUP, LEFT and SEARCH to do a "Partial Text Lookup. Goal: Lookup Product Price.
(34:34)Also: Look at Data Mismatch in lookup formulas.
10. (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.
11. (39:51) INDEX and MATCH to find Vendor for Low Bid. INDEX array argument is a row. Goal: lookup vendor name for lowest bid.
12. (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.
13. (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.
14. (51:23) How to use VLOOKUP & CHOOSE to do a lookup to multiple tables. Goal: Lookup Commission rate from Multiple Lookup tables.
15. (57:25) LOOKUP function with lookup_value and array arguments. Approximate Match. Goal: Lookup Commission and Rating. LOOKUP to enter fewer arguments.
16. (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.
17. (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.
18. (01:09:28) INDEX and ROWS to Flip Grade Table. Goal: Flip a Grade Table.
19. (01:13:20) Use MATCH to Compare Two Lists. Goal: Is an item in List 1 also in List 2?
20. (01:15:13) Use MATCH to Compare Two Lists. Goal: Is an item in List 1 NOT in List 2?
(01:16:40) Summary

Loading...


to add this to Watch Later

Add to

Loading playlists...