Master Excel Lookups And References

Discover the almost magical power hidden inside Excel's lookup-and-reference functions and transform your daily workflow

What you'll learn
Discover the almost magical power hidden inside the lookup-and-reference functions in MS Excel
Learn how to tame Excel’s lookup-and-reference power to re-engineer and drastically speed up your specific day-to-day tasks
The number one Excel concept you must learn before you learn anything else if you want to be taken seriously as a remotely savvy Excel user. This is hard-core Excel foundation stuff that everyone working with spreadsheets must know.
The increadibly easy-to-understand explanation of the anatomy of VLOOKUP—one of the most popular and misunderstood by beginners Excel functions.
How to work with named ranges in Excel and give your spreadsheet formulas a much more polished and professional feel while saving tons of time and eliminating certain types of pesky mistakes.
Tickle your creativity and imagination with the HLOOKUP function which can actually be as effective as the VLOOKUP when your Excel data is flipped 90 degrees.
Great Excel tips and tricks that you can learn immediately and start using every day.
Everything you need to know about the crazy-popular VLOOKUP function. (Including when NOT to use it.)
Astonishingly flexible ways to append specific data to your datasets by combining MATCH and INDEX functions. (When done right, it it’s way more efficient than using VLOOKUP!)
Design jaw-dropping, creative, elegant lookup and reference solutions for your spreadsheets.
Instantly understand the benefits of HLOOKUP in certain specific situations. (It’s easy. Especially if you already understand the VLOOKUP!)
Wrap your mind around the OFFSET function. In Excel, it’s probably one of the most misunderstood functions. Once you learn and understand it, you will be able to create simple, yet highly sofphisticated solutions to some of the most quirky lookup-and-reference problems. It may also help you quickly propel your creative-thinking skills to a more profound level.
How to create dynamic range references with the mega-useful esoteric OFFSET function. (So you can prevent your formulas from “breaking” in certain important situations!)
Learn how to use the little-known INDIRECT function to quickly consolidate data from multiple sheets. (When you do it right, all it takes is a single well-designed formula - no more copying and pasting links!)
Unveil the hidden world of array formulas in Excel. (80% of Excel users are probably clueless about this.)
How to TRANSPOSE data while preserving its connection to the original source. (Helps you save loads of time in the long run - when you want this functionality in your reports!)
How to create hidden lookup data arrays for flexible lookups. (So you can keep your spreadsheets crisp and clean of unnecessary junk!)
How to create flexible data lookups for Excel dashboards with user-friendly drop-down lists.
No Excel function is an island! Discover the super-specialized MATCH function. Pretty useless alone—on its own, it’s absolutely indispensable when you combine it with some other functions. Such as VLOOKUP or INDEX. It’s an absolute must-learn to create more elegant professional Excel solutions without completely racking your brains.
Discover what you can quickly accomplish once you learn to combine the VLOOKUP and MATCH in a symbiotic formula to step up from the one-dimensional world into a much more sophisticated two-dimensional one and achieve your goal in a more elegant way faster.
How to use the incredible INDEX function. Some Excel pros call it the “gold standard” of lookup solutions. When you learn how to use it you will achieve greater flexibility in your spreadsheets than you ever dreamed possible while creating solutions more elegant and simpler than you can believe.
Discover the heavenly marriage of INDEX and MATCH. When you learn how to properly combine these two functions you can instantly make it a breeze and a joy to solve the most complex data-lookup problems in your workflow. They are not only more flexible than using VLOOKUP but also more efficient and work faster in larger datasets.
A little-known method to easily consolidate data from multiple Excel spreadsheets into one by taking advantage of the INDIRECT function. Learn to create one simple formula that you can copy anywhere and it just works like a charm—without any modifications. No more copy-pasting!
How to use Excel’s CHOOSE function to create a formula to get the correct fiscal quarter from a date—it does not matter when your fiscal year begins.
Requirements
Make sure that Microsoft Excel is installed on your computer before you take this course.
This course is best if you have already covered the basics of Excel and use it every day.
Ideally, you are already somewhat familiar with Excel. This is an intermediate level course.
Description
I've developed this course for Stanford University.  Now, it's finally available to you as well. Join my Stanford students and learn, so that you too can invigorate and dominate your Excel workflow better than ever before!Enroll now and master Excel's most important and powerful lookup-and-reference functions. Propel your skills beyond VLOOKUP and discover the full power and hidden potential of Excel lookups.Few things can transform your Excel workflow faster than tapping into the true power of Excel lookup-and-reference functions.  Learn VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET, INDIRECT to immediately skyrocket your skills to a higher orbit.This course will demonstrate what you need to know about these amazing Excel tools, covering how to create powerful formulas that automatically find and display exactly what you want in your datasets.First, you'll master the venerable VLOOKUP and its remarkable potential, and how you can apply it to your workflow.Then you'll move on to more elegant solutions: discover the MATCH function and the benefit of  combining it with VLOOKUP.Next, you'll learn how to take advantage of INDEX and MATCH for ultimate flexibility.Plus, discover more efficient ways to consolidate data with the little-known INDIRECT function and tickle your creativity with the esoteric OFFSET function.After completing this hands-on training program, you'll be able to:Recognize the value of lookups and references to your specific workflowTake the full advantage of the following Excel functions: VLOOKUP, HLOOKUP, INDEX, MATCH, TRANSPOSE, and INDIRECTCreate flexible lookups for Excel dashboards with drop-down listsAppend detailed data to large datasetsCreate range-based lookups with the "approximate match"Find specific items using the "exact match"Create two-dimensional lookups by combining VLOOKUP and MATCHUse named ranges and Excel tables as reference listsLookup data from a "secret" tableOvercome the limitations of VLOOKUP by using INDEX and MATCHCreate automatically-expanding references with the OFFSET functionConsolidate data from multiple sheets with the INDIRECT functionUse TRANSPOSE function to flip your data 90 degrees with links to the original cellsAnd so much moreThis course will tickle your creativity and boost your Excel skills like nothing else.Enroll now, so you can begin to learn immediately!

Overview

Section 1: First Things First

Lecture 1 Download the Example Files

Lecture 2 Before You Begin to Learn

Lecture 3 Excel Cell References - Relative vs. Absolute

Section 2: Excel's VLOOKUP Function

Lecture 4 VLOOKUP Function Overview

Lecture 5 VLOOKUP With Approximate Match

Lecture 6 VLOOKUP With Exact Match 1

Lecture 7 VLOOKUP With Exact Match 2

Lecture 8 VLOOKUP With Exact Match 3

Lecture 9 Excel Named Ranges - Overview

Lecture 10 VLOOKUP With a Named Range

Lecture 11 Excel Tables - Overview

Lecture 12 VLOOKUP With an Excel Table

Lecture 13 VLOOKUP Exercise - Assignment

Lecture 14 VLOOKUP Exercise - Solution

Section 3: Excel's HLOOKUP Function

Lecture 15 Excel's HLOOKUP Function - Overview

Lecture 16 HLOOKUP Exercise - Assignment

Lecture 17 HLOOKUP Exercise - Solution

Section 4: Excel's MATCH Function

Lecture 18 MATCH Function - Overview

Lecture 19 VLOOKUP and MATCH Combined - 1

Lecture 20 VLOOKUP and MATCH Combined - 2

Lecture 21 HLOOKUP and MATCH Combined

Section 5: INDEX and MATCH

Lecture 22 INDEX Function Overview 1 - Rows and Columns

Lecture 23 INDEX Function Overview 2 Rows only or Columns Only

Lecture 24 INDEX Function Overview 3 - Reference Form

Lecture 25 INDEX and MATCH 1 - One-Dimensional Lookup

Lecture 26 INDEX and MATCH 2 - Two-Dimensional Lookup

Lecture 27 INDEX and MATCH 3 - Two-Dimensional Lookup

Lecture 28 INDEX and MATCH Exercise 1 - Assignment

Lecture 29 INDEX and MATCH Exercise 1 - Solution

Lecture 30 INDEX and MATCH Exercise 2 - Assignment

Lecture 31 INDEX and MATCH Exercise 2 - Solution

Lecture 32 Array Formulas - Introduction

Lecture 33 INDEX and MATCH in Array Formulas

Lecture 34 How to TRANSPOSE Data

Lecture 35 INDEX, MATCH, TRANSPOSE Array

Lecture 36 VLOOKUP with Named Arrays

Section 6: OFFSET Function

Lecture 37 OFFSET Function - Overview

Lecture 38 OFFSET - Dynamic SUM Range

Are you using Excel for any significant part of your daily workflow?,Are you a data analyst who needs to get data from diverse sources and quickly connect them by using Excel formulas and functions?,Are you a finance professional searching the best ways to work with financial reports in Excel?,Are you a logistics analyst and want to improve your specific Excel data workflow and make it more efficient automatically?,Are you a free-lance consultant who wants to significantly slash time that you now waste staring at Excel spreadsheets and spend more time serving your valuable clients?,Are you an admin assistant who would like to become crazy-efficient in Excel. So you can have more time to perform your other important duties and impress your bosses at the same time?,Are you a student who would like to master Excel spreadsheets and learn pro-level techniques and the best practices?,Are you a marketing professional who would like to get useful actionable information from your market-data dumps quickly and easily?,Are you an accountant and need to quickly prepare Excel reports that read data from multiple sources and get them to update automatically?,Are you an operations professional who gets Excel datasets from all over the world and wants to learn how to use formulas and functions to beat that data into highly-usable shape.,Are you someone who wants to be more creative in Excel and learn how to solve problems in the best and quickest possible way?