VLOOKUP explained in simple terms

In the following lesson we cover the topic of VLOOKUP explained in simple terms. The first place to start is what VLOOKUP is trying to achieve and to understand it in human terms.

Look at the table below and tell me the number of points that ‘Manchester United’ obtained in the 2013/ 2014 season?

Congratulations you have performed your first VLOOKUP. The process you followed is exactly what Excel will do to perform a V(ertical) LOOKUP.

Consider the process your brain followed and look at the picture below. Your brain:

1. Identified what is being looked up (Manchester United)
2. Identified where the list of team names were and looked down them
3. When it found the exact match it stopped and looked across the row
4. When it got to the 9th column it recorded that Manchester United received 64 points in the 2013/ 2014 season.

This is all that is required to do a VLOOKUP in Excel.

In fact the syntax for a VLOOKUP could be summarised as:

• Lookup Value (Manchester United in this case),
• Table where the lookup value is (your brain just sees it but Excel needs to be told where to look),
• What column number do you want to extract (your brain assumes it is the 9th column- Excel will need to be told this),
• What type of lookup do you want to do (Excel can find an exact or approximate match- MUCH more about this later).

There are special things you can do with each of these options but at the most basic level that is all it does.

Watch the video clip of how we create the VLOOKUP for the above example

To follow along go to 1_0

In the following lessons and examples (with downloadable VLOOKUP examples and exercises), you will learn about each component and all the great things you can do with VLOOKUP. These include:

• 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
• Let Excel guestimate an answer
• Create categories for employee performance, grouping of sales, grouping employees into age brackets
• Age invoices, stock and debtors into bands- aging anything
• Sales commission bands
• 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

Let’s now move onto the computer.