Udemy
    •  
    •  
    •  
    •  
    •  
    •  
    •  
    •  
Turn what you know into an opportunity and reach millions around the world.
Learn More
Your cart is empty.
Keep shopping
Lookup Functions in Excel
Rating: 4.7 out of 5(10 ratings)
44 students

Lookup Functions in Excel

Learn VLOOKUP, INDEX, MATCH & XLOOKUP for powerful data retrieval, dynamic lookups, and advanced Excel skills.
Created bySimon Sez IT
Last updated 11/2024
English
English [Auto],

What you'll learn

  • Use VLOOKUP, HLOOKUP, INDEX, MATCH, and XLOOKUP functions.
  • Implement OFFSET and INDIRECT functions for dynamic data selection.
  • Perform two-way lookups efficiently.
  • Utilize CHOOSE and SWITCH for conditional data retrieval.

Course content

2 sections • 17 lectures • 1h 42m total length

  • Course Introduction2:16

    Master lookup functions in Excel, from Vlookup and Hlookup to index and match, Xlookup, offset, indirect, and two way lookups, with practical exercises.

  • WATCH ME: Essential Information for a Successful Training Experience2:11

    Watch this video to access downloadable exercises and instructor files, learn how to download, unzip, and match files to each exercise, and adjust playback settings for the best viewing.

  • DOWNLOAD ME: Course Files0:24
  • DOWNLOAD ME: Exercise Files0:24
  • Using VLOOKUP Part 1 (Exact Match)10:39

    Explore how to use Vlookup for exact and approximate matches in Excel, including lookup value, table array, and column index, plus error handling with ifna.

  • Using VLOOKUP Part 2 (Approx Match)4:24

    Use VLOOKUP with the true approximate match when the lookup value does not exist exactly, returning the marginal tax rate from the salary bands.

  • Using HLOOKUP5:38

    Learn how to use hlookup for data that runs horizontally, including transposing data, creating a named range, and performing exact matches to retrieve year, rating, and genre.

  • Using INDEX and MATCH10:26

    Explore how index and match overcome vlookup limitations by performing flexible lookups independent of column order, using exact match and named ranges to retrieve category, revenue, and profit.

  • Using XLOOKUP and XMATCH10:07

    Explore Excel 2021's XLOOKUP and XMATCH as simpler alternatives to index and match, detailing mandatory vs optional arguments, not found handling, exact match, and search modes including first-to-last and last-to-first.

  • The OFFSET Function10:48

    Explore how the offset function moves from a reference cell to return a dynamic range, then combine it with sum to compute last six months of data.

  • The INDIRECT Function9:05

    Use the indirect function to reference other cells and build dynamic totals, including sum with regional named ranges, r1c1 referencing, concatenation, and auto-update of the last column.

  • Exercise 015:02

    Practice excel lookup functions by creating a data validation drop-down of athletes and retrieving bib number, route, and position with index and match or vlookup, and handle not found errors.

  • Section Quiz

Requirements

  • Basic Excel knowledge.
  • Understanding of Excel functions.
  • Access to Excel 2016 or later.

Description

**This course includes downloadable course instructor files and exercise files to work with and follow along.**


Welcome to our "Lookup Functions in Excel" course, where you'll learn essential tools for efficient data retrieval and manipulation. In this comprehensive training, you'll explore various lookup functions, including VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP, and more. You'll discover how to use VLOOKUP for exact and approximate matches and explore alternative methods like INDEX and MATCH for more advanced lookup scenarios.


Throughout the course, you'll also delve into lesser-known functions like OFFSET, INDIRECT, CHOOSE, and SWITCH, expanding your repertoire of Excel skills.


By mastering these lookup functions, you should be able to perform two-way lookups and efficiently retrieve data from large datasets. Whether you're a student, professional, or business owner, this course will enhance your ability to organize, analyze, and interpret data effectively, making you an asset in any setting.


In this course, you will learn how to:

  • Use VLOOKUP, HLOOKUP, INDEX, MATCH, and XLOOKUP functions.

  • Implement OFFSET and INDIRECT functions for dynamic data selection.

  • Perform two-way lookups efficiently.

  • Utilize CHOOSE and SWITCH for conditional data retrieval.


This course includes:

  1. 1 hour 40 minutes of video tutorials

  2. 14 individual video lectures

  3. Course and Exercise Files to follow along

  4. Certificate of completion

Who this course is for:

  • Anyone who wants to become more efficient with Excel functions.
  • Students learning Excel functions for school projects or future job opportunities
  • Professionals who work in any field where you deal with data, such as finance, marketing, or research.
  • Business owners who handle lots of data.

Report abuse