VLOOKUP. You might have seen it in an excel model before. But do you know how to use it?
Intro to Excel model training
This is part of a series on Excel model training focused on helping founders and investors to actually enjoy and get proficient in excel. It’s such an important skill to have, and there’s no excuse to not be able to do all the ‘advanced’ stuff, let alone the basics.
This is a pragmatic course where you will only learn the most useful things; those that I actually use regularly. We’ll go through the obvious applications, but also include some hacks I’ve developed over the years.
To get the excel example for this and all the other instalments, subscribe with the pretty box on the right and you’ll get the excel tip of the day sent directly to your inbox. Within a month and ten minutes practice, you’ll be a boss in no time.
You can join up to the training here.
What’s the point of VLOOKUP?
Excel is super useful to import information and then to manipulate it. Say you import your client data from Pipedrive or Salesforce. Now you want to use that data set to input it into your analysis. But how?
VLOOKUP is a way to search a database for one variable (such as client name) and return the value that you want (such as the ARPU). Sounds useful, right?
VLOOKUP is suuuuuuuuper important! I literally use it every day. It’s without a doubt one of the most important things you can know. You have to be a boss at it!
How does VLOOKUP work?
This is the structure of a VLOOKUP formula in the wild:
Excel always gives you a hint how to structure a formula, You can see the blue text at the bottom.
The description of this would be:
=VLOOKUP(
1. Value (like client name) you want to look for,
2. Dataset where you want to lookup the value (select all the data),
3. the column number in the range containing the value you want (like ARPU),
4. Exact Match or Approximate Match – which you decide by 0 = FALSE or 1 = TRUE)
Useful hints
- Always set the first column with the most useful index, like the name of your client. Vlookup looks for data using the first column.
- Always use FALSE. I have never used TRUE. Like ever. Approximate values are zero use. Never use TRUE.
- When you want to figure out the column you want to find the data from, always include the first column (the name).
- If you don’t want to deal with counting columns to figure out what the index number is, use the embedded count function. It’s a super useful hack I invented.
- You will see an ‘#N/A’ if what you’re looking for in the database isn’t there.
- If you don’t like to see #N/A then either clean up the database or wrap a IFERROR() or IF(ISERROR() around the VLOOKUP (This is the easier thing to do)
- If you see a ‘#VALUE!’ error or a ‘#REF!’ error then you have messed up. Pay more attention…
Example
The excel example sheet will help you to learn this with an example.
To get the training model, subscribe to the series below.