Skip to content Skip to site navigation Skip to service navigation

Master Excel Lookups and References

Most Technology Training classes will be delivered online until further notice.

Before each sesson, Tech Training will provide a Zoom link for live online classes, along with any required class materials.
 


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
Igor Ovchinnikov

Igor Ovchinnikov has been teaching software productivity classes for over 20 years. Stanford staff have enjoyed his training since around 2007. His training philosophy stems from a strong belief that learning is a perpetual cycle of new discoveries and improvement.

Custom training workshops are available for this program

Technology training sessions structured around individual or group learning objectives. Learn more about custom training

University IT Technology Training sessions are available to a wide range of participants, including Stanford University staff, faculty, students, and employees of Stanford Hospitals & Clinics, such as Stanford Health Care, Stanford Health Care Tri-Valley, Stanford Medicine Partners, and Stanford Medicine Children's Health.

Additionally, some of these programs are open to interested individuals not affiliated with Stanford, allowing for broader community engagement and learning opportunities.