Bottom line: Learn how to use Excel’s most commonly used Function VLOOKUP, explained in the easiest way and also visually so that you understand it well!
Skill level: Beginner
VLOOKUP searches for the value you specify and returns a matching value from another column. More technically, the VLOOKUP function looks up a value in the first column of the specified range and returns a value in the same row from another column.
In its common usage, Excel VLOOKUP searches through your data set based on the unique identifier and brings you a piece of information associated with that unique identifier.
Official VLOOKUP Syntax as explained by Microsoft:
VLOOKUP is an Excel function to lookup and retrieves data from a specific column in the table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The “V” stands for “vertical”. Lookup values must appear in the first column of the table, with lookup columns to the right.
Lookup a value in a table by matching on the first column
The matched value from a table.
Let’s understand VLOOKUP function with an easy example: Suppose We have employees Paysheet report as you can see below
For the reference, we will call above paysheet table as Report table. We want to retrieve Gross Salary (Column E) and Nationality (Column F) of by employee number (Column B) from another location which would be our Lookup Array or Lookup Table which you can find below:
For the references, we would call above table range as a Lookup table. From this lookup range or table, we are looking for Gross Salary and Nationality employee wise.
Now let’s write VLOOKUP Function step by step (Visually)
From the above animation, we can see that what are the arguments with our examples. Please note that the first three arguments are compulsory and the fourth argument is optional which is the type of match we are looking for. By default, VLOOKUP function applies 1 or TRUE or Approximate match (I will explain you Approximate matches and wildcards in another post). But we are looking for an Exact Match in this scenario, so we will be selecting either FALSE or 0.
Below is another image showing VLOOKUP Formula explained in both Report table and Lookup table to reinforce the formula syntax concept:
Column Index number will be 6 instead of 5 when we would write the formula in Nationality column in Report table (paysheet, left the table). Please note that we can also select table array till column O instead of P since we are looking for Gross Salary in Report table in column E, but since we want table array to be consistent we should select full table array till column P.
The completed table after the formula is written will look like as follows (Report Table/Paysheet)
Following are key points to remember while using VLOOKUP Function (Caution)
- VLOOKUP always looks up values from Left to Right, it is not capable of looking values from right to left, so in above example, if employee number was in the second or third column in our Lookup table/lookup array/Master records table, VLOOKUP function would not have worked (Index and Match Function together would be the best option, I will cover them in different post). In other words, the Lookup value column MUST be the very first column in our Lookup array range or table.
- Excel VLOOKUP is case-insensitive, meaning that uppercase and lowercase characters are treated as equivalent.
- Lookup values should always be unique parameters in Lookup Array (range/table), otherwise, Vlookup will only return the first matching value. In above example, if we were to look up for Nationalities of employees as Lookup value instead of Employee number, Vlookup would return first matching value corresponding to the Nationality value, eg if there were two employees with USA nationality, VLOOKUP would return first one only and rest USA nationality employees would be ignored.
Besides the above limitations, if you follow VLOOKUP rules strictly, it is quite handy and powerful Excel Function, and helpful in creating Dashboards and reports on the go!
If you have found this post and tutorial useful, please do not forget to share it with your friends and colleagues. Also, share it on social media! As promised I will do one more post covering VLOOKUP advanced functionalities, so stay tuned and subscribe to my newsletter so that you won’t miss the tutorial.