Lookup Functions and Formulas in Excel 2016 - Comprehensive Lessons with 20 Examples

Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Dive into an extensive 1-hour 19-minute video tutorial on Excel 2016 lookup functions and formulas. Master 20 practical examples covering VLOOKUP, HLOOKUP, INDEX, MATCH, and LOOKUP functions. Learn to create invoices, perform two-way lookups, calculate taxes from complex tables, and compare lists. Explore advanced techniques like partial text lookups, lookup left operations, and flipping grade tables. Gain proficiency in using Data Validation, IF, ISBLANK, LEFT, SEARCH, ROWS, and COLUMNS functions in combination with lookup formulas. Perfect for Excel users seeking to enhance their data analysis and manipulation skills.

Syllabus

) Introduction.
) Data Validation List. VLOOKUP, Exact Match. IF & ISBLANK functions. Goal: Create Invoice. .
) HLOOKUP with Exact Match Lookup. Goal: Lookup Tyrone's Phone Number. Horizontal Lookup is rare..
) VLOOKUP with Approximate Match Lookup. Goal: Lookup Pipe size..
) VLOOKUP with Approximate Match Lookup. Goal: Lookup Commission Paid..
) VLOOKUP with Approximate Match Lookup. Cell Reference for Column Index Number. Goal: Get Rating and Commission..
) VLOOKUP and MATCH functions with Exact Match Lookup. MATCH for Column Index Number. Goal: Retrieve employee data..
) VLOOKUP & ROWS or COLUMNS with Exact Match Lookup. ROWS or COLUMNS for Column Index Number. Goal: Retrieve record from Employee Table..
) Multiple VLOOKUP functions. Goal: Calculate taxes from complex tax table..
) How to use VLOOKUP, LEFT and SEARCH to do a "Partial Text Lookup. Goal: Lookup Product Price..
)Also: Look at Data Mismatch in lookup formulas..
) 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..
) INDEX and MATCH to find Vendor for Low Bid. INDEX array argument is a row. Goal: lookup vendor name for lowest bid..
) INDEX and MATCH functions to do a Two-Way Lookup. INDEX array argument is a table. Goal: Do Two-Way Lookup to find Discount..
) 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..
) How to use VLOOKUP & CHOOSE to do a lookup to multiple tables. Goal: Lookup Commission rate from Multiple Lookup tables..
) LOOKUP function with lookup_value and array arguments. Approximate Match. Goal: Lookup Commission and Rating. LOOKUP to enter fewer arguments..
) 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. .
) LOOKUP with lookup_value and lookup_vector and result_vector arguments. Approximate Match. Goal: "Lookup Adding" to total costs. LOOKUP to do Array Operation..
) INDEX and ROWS to Flip Grade Table. Goal: Flip a Grade Table..
) Use MATCH to Compare Two Lists. Goal: Is an item in List 1 also in List 2?.
) Use MATCH to Compare Two Lists. Goal: Is an item in List 1 NOT in List 2?.
) Summary.