Vlookup tutorial


Instructions and exercises focused on all the various aspects of VLOOKUP including both the approximate and exact matches.

By learning about this single formula (10 minutes at most) you will be able to do all the processes listed below.

Categories: , .


Vlookup tutorial

One of the most important formula in Excel is the VLOOKUP formula.

It has a variety of practical uses and is the stepping stone into truly understanding how Excel thinks.

The  ‘VLOOKUP Explained in Simple Terms’ course is a series of VLOOKUP lessons that will start you at the basics and turn you into a VLOOKUP Pro. It includes VLOOKUP practice exercises and quizzes.

Some of the practical uses for VLOOKUP are:

  • Joining databases and tables e.g. attach employee names to there salaries, address details etc
  • Find missing items (useful for reconciliation)
  • Compare 2 columns (useful for reconciliation)
  • Make areas static, anchor data to work with e.g. extract the marketing cost from each months income statement, no matter which line is appears on
  • Let Excel guestimate an answer e.g. which tax band does the employees salary fall into
  • Create categories for employee performance, grouping of sales, grouping employees into age brackets, debtors days e.g. employees less than 20 years old, between 20 and 30, 30 and 50, 50 to 65, over 65.
  • Age invoices, stock and debtors into bands- aging anything e.g. all debtors current, 0 to 30 days, 31 to 60 days, 61 to 90 days and more than 90 days
  • Sales commission bands e.g. commision on sales below R100 000, between R100 000 and R250 000, more than R250 000.
  • Tax bands
  • Different interest rates at different bank balance levels
  • Create costs that affect certain months depending on business activity
  • Calculate step up costs and fixed costs in a financial model e.g. rents increase when we produce over a million widgets.

The modules covered include:

  • VLOOKUP Exact Match
  • VLOOKUP Approximate Match
  • Why is VLOOKUP Not Working
    • Vlookup #ref error
    • Vlookup #value error
    • Vlookup #name? error
    • Vlookup not returning correct value
    • Vlookup #n/a
  • VLOOKUP Tips and Tricks
    • Replace vlookup #n/a with 0,blank, text or number
    • Excel ageing formula
    • Partial vlookup match- contains part of the value
    • Vlookup multiple values

Preview the course at the How to use VLOOKUP page