As a financial professional, I use vlookup on an everyday basis. But for some of you who do not use Excel that often, using Vlookup might be a painful experience, either with other people’s help, or with Excel formula insert function.
Is it that hard to use? Maybe not. I will help you understand it and remember it forever.
Firstly, what is Vlookup? Why do you need it so much?
I have a nick name for it: Vlinkup. Why? Because most of the time, it is the most handy tool you can use to Link two spreadsheet. (Remember it, you will need it to remember the formula)
Here is the example you need to solve:
You have a table of fruit orders with weight of orders, you want to link it to another spreadsheet, which includes the prices to calculate the total price to pay.
How can you use Vlookup to solve it?
Now the key is the shared same data (column) between the two spreadsheets, the maze is the area which includes the shared same data and the value you want to fetch.
There are three factors you need to remember in the Vlookup formular: Key (shared same value), Maze (the area which includes the shared value and the value you need to fetch to the other spreadsheet), and the steps, how many columns between the shared value and the fetched value, as shown below:
Ps: the last parameter, in practical work, always input as “false”.
Now close your eyes and draw the picture of the maze in your mind. Next time when you want to use vlookup, think of the picture, you will know the formula, you have my word ：） :
And here are some bonus, 3 tips for vlookup formula:
- Sometimes when you get #N/A with vlookup, don’t forget to check the format of the key value. It is highly possible they are both numbers and one of them is formatted as text.
- Always make sure to add $ mark to your search area (your maze), the reason is that you can only get consistent result by absolute reference. It is all right that you don’t understand. Just remember, we need MONEY.
How you do it? Select the whole area string, such as “[Book2]Sheet1! A2:D6” and press F4.
- When the Maze area is too big, you don’t need to use your finger to count column after column. Use your mouse to select the area, and the small text box beside your cursor can tell you.
The principle I applied in this article is inspired by Tony Buzan’s book 《The most important graph in the world》
And I want to thank his co-author Jennifer Goddard for her kindness to sign my copy. 🙂
I highly recommend it to everybody, it could be a inspiration for you to change your life.