Available to Stanford University staff, faculty, or students.
Invigorate your Excel workflow! Master the most important lookup-and-reference functions - in one day. Learn to use VLOOKUP, HLOOKUP, INDEX, MATCH and other powerful tools. Propel your skills beyond VLOOKUP and discover the full power 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 skill to a higher orbit.
This class 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, we'll cover the venerable VLOOKUP and its remarkable potential, and how you can apply it to your workflow.
Then we'll move on to more elegant solutions: discover the MATCH function and the benefit of combining it with VLOOKUP.
Next, we'll cover how to take advantage of INDEX and MATCH for ultimate flexibility.
Plus, discover efficient ways to consolidate data with the little-known INDIRECT function and tickle your creativity with the esoteric OFFSET function.
After completing this one-day hands-on training session, you'll be able to:
- Recognize the value of lookups and references to your specific workflow
- Use and implement the following Excel functions: VLOOKUP, HLOOKUP, INDEX, MATCH, TRANSPOSE, and INDIRECT
- Create flexible lookups for Excel dashboards with drop-down lists
- Append detailed data to large datasets
- Create range-based lookups with the "approximate match"
- Find specific items using the "exact match"
- Create two-dimensional lookups by combining VLOOKUP and MATCH
- Use named ranges and Excel tables as reference lists
- Lookup data from a "secret" table
- Overcome some limitations of VLOOKUP by using INDEX and MATCH
- Create automatically-expanding references with the OFFSET function
- Consolidate data from multiple sheets with the INDIRECT function
- Use TRANSPOSE function to flip your data 90 degrees with links to the original cells
- And so much more
University IT Technology Training classes are only available to Stanford University staff, faculty, or students. A valid SUNet ID is needed in order to enroll in a class.
