I know; it is quite a difficult task to remember how to use VLOOKUP correctly. If you have learnt it recently or don’t use it quite often, the chances are, in few days you will forget it.
In this article, I am going to explain VLOOKUP with a simple example which will act as an easy reminder for you. Whenever you get stuck, look at this VLOOKUP example, refresh your learning and move on just within a minute.
For this example, we have two tables. In one table, we have the personal ID Nos., Names of Employee and their phone numbers. In second table, we have a few IDs and we are interested to get the Name of Employees and phone numbers against these.
Formula for VLOOKUP
=VLOOKUP(Lookup_value, table_array, col_index_num, [range_lookup])
To remember, you should read it as following
=VLOOKUP(reference or key, which table to look out for this key, what column number the result is located, 0)
As you know VLOOKUP is used to lookout any value from a table based on some input. It is unlike “Find” where you directly search a value. With VLOOKUP you provide a reference (or key) to get corresponding value from a data table.
If you wish, you can click on the “insert function” icon to get the VLOOKUP “Function Arguments” dialog box. This is an easier way to insert VLOOKUP formula. However, here also you get confused sometimes.
Why do we forget VLOOKUP formula
- We forget how to select the table_array.
- We get confused what to write for range_lookup.
- We don’t remember how to count the column index number.
How to remember VLOOKUP forever
There are four arguments in the VLOOKUP Syntax. If you understand about these arguments well, you won’t forget the formula anymore. I will give you simple “mantra” to remember it – “CUSTOMER FIRST”.
- Consider Lookup_value as a customer and ‘matching value’ is what he is looking for. Remember – “CUSTOMER FIRST”. So, Lookup_Value should be the first thing in your formula.
- Now let’s explore what this customer is looking for and select the area where you will get the matching value. For our second argument also, we should not forget “CUSTOMER FIRST”. Yes, the Lookup_Value must be in the FIRST column of the table_array you select. And then, you should extend the selection until you include the column where the matching value is placed. Of course, you are free to go beyond that.
- For third argument also, the mantra is “CUSTOMER FIRST”. Start the counting from the first column of table_array (column with Lookup_value) and go till you find the matching value.
- No need to do anything for the last argument. In almost all cases you will use false or 0. Make a habit to put a zero (0) at last.
Explanation of VLOOKUP Example
Based on the above mantra, let’s understand the above example.
We are looking for the “Name of Employee” in “Table – 2”. For searching this value in “Table – 1”, our key is the personal ID. So our Customer is Personal ID or F5.
Now to define the table_array, we will look out for “Personal ID” the column in “Table – 2” and select the table_array starting from this column. Hence, our first column in table_array is B. We will start our selection from B5 Cell. We will select until we find the “matching value” which in our case is “Name of Employee” in column C. So we have to continue until C11. I have selected one more column (till D11) just to illustrate that it does not matter if you extend the selection beyond that.
The counting starts from the Customer which is Personal ID in this example. The “matching value” is positioned at number 2 column from it. If we want to get the phone number, this counting will become 3.
That’s all my friends! It’s really simple to remember VLOOKUP like this.
Please note that I have only explained the basics of VLOOKUP function with the above example. There is a lot more to learn. If you have not already, take a look at my comprehensive VLOOKUP tutorial.
Here is the excel file used for this example. As your home work, I have left the three cells blank.
I hope you will not forget VLOOKUP formula anymore. But if you are still not confident, bookmark this page and have a look at it whenever you wish to.