MS Excel 2019 Advanced VLOOKUP Formulas and Pivot Tables

Mastering the use of most popular LOOKUP'S and Pivot Tables will allow you to manipulate, extract and Analyze information like never before! The learners becomes experts after following this Video Course. This Complete course is About LOOKUP and References from Formulas and Complete Pivot Tables in Excel. Explained by Industry experts with all their experience here.

This course is recorded with the latest version of Excel 2019. All the lectures are explained with the examples and you can download all these excel files and practice the formulas.

This Course improves your Excel workflow faster with the true power of Excel lookup-and-reference functions.

Course Overview

Pivot Tables

90% of Excel users do not use Pivot Tables but those that do save hours of time and become valuable assets in their companies.You learn Excel Pivot Tables & Pivot Charts quickly and effectively, download the companion exercise files so you can follow along with the instructor by performing the same actions he is showing you on the videos.

By the end, you will be an Expert user of Pivot Tables, able to create reliable analyses which are able to be drilled-down quickly, and you'll be able to help others with their data analysis.

Look up Function

Excel makes it easy to search for values, both in rows and columns. While there are advanced functions also that enable Excel users to search for data "database-style", the HLookup and VLookup functions are their simpler-yet-powerful Functions.

VLOOKUP was launched in 1985. It has been with Excel from the beginning; it was included in Excel 1 for Macintosh released in 1985. For 34 years, VLOOKUP has been the first lookup function learned by Excel users and our 3rd most used function (after SUM and AVERAGE).

Topics Covered

• VLOOKUP to the Left

• VLOOKUP to Replace Nested IF Formula

• VLOOKUP Multiple Tables

• VLOOKUP Multiple Criteria

• VLOOKUP Different Sheets with Same Formula

• VLOOKUP Auto Update Column Number

• Use of wildcard in VLOOKUP

• Nested VLOOKUP Trick

• 3D LOOKUP with VLOOKUP

• 2D LOOKUP with VLOOKUP

• HLOOKUP, INDEX, MATCH, TRANSPOSE, and INDIRECT

Learning Outcomes

• Be an Expert using Powerful Excel functions: VLOOKUP, HLOOKUP, INDEX, MATCH, TRANSPOSE, and INDIRECT

• Consolidate data from multiple sheets with the INDIRECT function

• Create automatically-expanding references with the OFFSET function

• Create flexible lookups for Excel dashboards with drop-down lists

• Create range-based lookups with the "approximate match"

• Create two-dimensional lookups by combining VLOOKUP and MATCH

• Find specific items using the "exact match"

• Overcome the limitations of VLOOKUP by using INDEX and MATCH

• Recognize the value of lookups and references to your specific workflow

• Use named ranges and Excel tables as reference lists

• Use TRANSPOSE function to flip your data 90 degrees with links to the original cells