How To Use Vlookup in Excel?

by | Nov 14, 2019 | Data Analytics

11 Min Read. |

Using Excel for most of your time in office and still can’t get enough of how many different commands it holds inside? Don’t worry, we have been there, and to be honest, we are still learning new commands day in day out on Excel. Today we are going to discuss Vlookup in excel and how to use vlookup in excel to get your work done more efficiently.

“The goal is to turn data into information, and information into insight.” – Carly Fiorina

Even if someone has been working on Excel for a long time, if you ask them about something, they might get confused as there are multiple ways to do the same thing. So today, we will be talking about how you can use Vlookup in your excel file to find a value in a multi-table column.

But before we start, we would like to mention the benefits of using Vlookup and how it can save your time.

What is Vlookup Function in Excel?

Whenever you want to take out the information from your excel table, you must use the VLOOKUP function in excel. It will surely save you time. Moreover, the ability to find and retrieve the desired information from the given table is undoubtedly a blessing for many excel users.

In addition to this, VLOOKUP is reasonably easy to use once you get hold of it. If not used in a proper way, there are number ways your data will show the wrong stats, so you need to be careful.

Download Detailed Curriculum and Get Complimentary access to Orientation Session

Date: 28th Nov, 2020 (Sat)
Time: 11:00 AM to 12:30 PM (IST/GMT +5:30)
  • This field is for validation purposes and should be left unchanged.

So coming to the question, VLOOKUP is function embedded in the excel software, which helps the user retrieve the data from a particular column in a given table. VLOOKUP uses approximation and exact matchmaking along with wildcards (*?) when information matches partially. V in VLOOKUP stands for vertical.

While the LOOKUP column needs to be on the right, the LOOKUP value must appear in the first column of the table. VLOOKUP function in excel resolves the purpose of matching the value of a table from the first column of a user’s table. On the other hand, the function returns the matched value from the table.

The syntax for VLOOKUP function:- =VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

Benefits of Using Vlookup in Excel

Now let’s talk about the benefits of why you should be using Vlookup for retrieving your data in excel.

(i) First things first, extracting data manually from an extensive data set in excel can be quite tricky and time-consuming.

(ii) If you use Vlookup in excel, you only need to put the function once, and it will dynamically extract your required data from the table.

(iii) Furthermore, it is not case-sensitive, meaning you don’t need to worry about using capitalization to retrieve your information.

(iv) With the help of Vlookup function in excel, you can easily merge the data from the different tables.

(v) Moreover, you can compare the data between the two excel workbooks without having to face any errors.

(vi) You can use this function to fetch the exact match (default,0, FALSE) for the given value from the table.

(vii) On the contrary, Vlookup in excel can fetch information based on the approximation match (optional,1, TRUE).

(viii) With the help of Vlookup, you can classify and categorize a specific set of data from your table.

(ix) Similarly, the function uses wildcard characters for partial matching.

(x) Lastly, the Vlookup function can be used to replace the Nested IF function.

Difference Between Lookup, VLookup, & HLookup

To understand completely, the functions take full advantage of their usage in excel. You must know their specificity.

First, the Lookup function allows the user to search the desired data from a row or column and in return, provide the corresponding piece of data.

Secondly, the Vlookup works similarly but allow its users to have a vertical search and gives out the result in the left to the right procedure.

Last is the Hlookup function, which, as it stands, does the same data extraction thing but only in a horizontal line.

(As you can see in the image, both Vlookup and Lookup are showing the same result, but Lookup function as less restriction along with a smaller command to work )

Lookup Has The Upper Hand On Vlookup

Since it’s inception in 2016 by Microsoft Lookup function combines both Vlookup and Hlookup in one function. Most people don’t know about it as it’s a reasonably new command.

(i) With the help of Lookup, now the user can search for the result both vertically and horizontally in a table.

(ii) Lookup provides both left-to-right and right-to-left procedure, whereas Vlookup can only work from left to right.

(iii) Besides, you can easily audit your VLookup in excel help of F2.

Now once you clear with the usage and the difference between different lookups, we can no move to step by step tutorial of how you can put Vlookup in your excel table and use it to find the required data.

Download Detailed Curriculum and Get Complimentary access to Orientation Session

Date: 28th Nov, 2020 (Sat)
Time: 11:00 AM to 12:30 PM (IST/GMT +5:30)
  • This field is for validation purposes and should be left unchanged.

Example – 1 Vlookup In Excel To Find An Exact Match

Vlookup has two modes of operation, so first, we will be giving you the example of an Exact match of Vlookup in excel, and then we move to the approximate match. Most of the time, you will only need to have an exact match.

That’s because when you are looking for the information based on a specific key like income, name, office ID, you have data stored definitively, so you don’t need an approximative match.

Step – 1

For this Vlookup in excel example, you need to create your table on excel and enter the required data in rows and columns.

As you can see here, we have created a rather small table 15 data sets only for the better explanation you can use Vlookup function in excel for any number of data.

In the first column, we have “Employee ID,” ranging from 416 to 430.

Right next to employee ID, we have a “First name” column, which holds the data of the first name of the companies employees.

“Second name” is our third column holding the last names of the employees.

The last column, “Monthly Income,” holds the data for the monthly income of each employee.

Step – 2

Now create one more table which will show the result of Vlookup after data extraction.

(In the given image we have taken the Employee ID as the Lookup_value, and monthly income will increase the output of the respective Employee ID by using the Vlookup in excel)

Note:- We have used Employee ID in our result table because of the limitation of Vlookup which is it can only take the Lookup value from the first column of the table

Step – 3

Now we come to the main part where you will be adding the Vlookup function in your result table.

(Now we put the function in the table, as you can see here we have taken the lookup_value in the formula equals to A2 which is the first row of Employee ID column)

(Now we select the table_array for the function which as you can see we have used A9: D24)

(Now we select the col_index_num which is 4, meaning we have chosen the monthly income column data to be extracted from Vlookup in excel)

(At last, we set the range_lookup to be 0 which means to find the exact match result of monthly income based on the Employee ID column)

Now once you are done with putting the Vlookup function in your result table, you can start using the result table to find the desired data respective to your query.

Step – 4

Finally, we will add some employee IDs in our result table to find if the Vlookup function is working as we expected and showing the monthly income of the respective employee ID.

As you can see here, we have inserted employee ID 417, 418, and 419 in the result table, and with the help of Vlookup function, we get the monthly income for the employee ID from our first table, which is right below our result table.

Breaking the Vlookup function in Excel for better understanding

Now you clear with the vlookup in excel example we would like to elaborate on the Vlookup function and explain each of its arguments.

1. lookup_value

The value you want the function to look for in the first column of a table.
For example, you want to find the monthly income of employee ID 416 from cell D10

2. Table_array

This the cell range from where your function will take out the value. You can use the cell range from the existing worksheet, or you can take another excel file. In our example, we will take the employee ID corresponding to other labels on our table.

Download Detailed Curriculum and Get Complimentary access to Orientation Session

Date: 28th Nov, 2020 (Sat)
Time: 11:00 AM to 12:30 PM (IST/GMT +5:30)
  • This field is for validation purposes and should be left unchanged.

3. Col_index_num

The column of the given table from where the function will retrieve the value. In this vlookup in excel example, we will take monthly income as the extraction data, which has the column index number equals to 4.

4. Range_Lookup

The last field is used to define how close a match should exist between your lookup result and the value of your leftmost column in the lookup table. We have taken the exact match for this example.

(i) (Default). FALSE = EXACT MATCH
(ii) (Optional). TRUE = APPROX MATCH

Example – 2 Vlookup In Excel To Find An Approximate Match

We create another Vlookup in excel example for a better understanding of the approximate match. Now we will use the grading system and find the grade of an individual by using Vlookup function concerning the mark obtained by the student.

Step – 1

Create a table according to your requirements.

(We have created a table that shows the marks in the first column and the grade respective of the marks)

Step – 2

Creating a second table that will show the result of your Vlookup in excel.

(Here we are going to put the Vlookup excel function in the grade row)

Step – 3

Now we add the function to the cell.

(On step-3 we add the same function that we did in example-1)

E28 – It refers to the lookup_value

A28: B35 – Table_array from where the function will extract the required information.

2 – col_index_num shows the column number of the table from where the data will be retrieved. In our example, its the grade column from the table.

TRUE – This argument depicts how close the match should exist. In our example, we are using an approximate match. Thus, we need to put TRUE as a value manually.

Step – 4

Use the result table and find out if your Vlookup is working correctly.

Right now, as you can see, we added a score in result tables such as 88,22 and 46. None of them were mentioned in our first table, but still, we didn’t get any error.

On the other hand, the function comes up with the result (Grade), which is B, F, and D, respectively.

With these two examples, you get a better understanding of how both exact and the approximate match work and where you can use one of them.

Click here to check out this video for a visual understanding of how to use Vlookup in Excel.

Things To Keep In Mind While Using Vlookup In Excel

There are a few things that you need to keep in the back of your head while using this function, given below are some of them.

(i) First, by default, Vlookup function uses an approximate match.

(ii) Secondly, to perform the approximate match, your data needs to be in the sorted form.

(iii) In addition to this, you can use absolute references if you want to retrieve the data from more than one column.

(iv) In Vlookup function, you can use the name for the ranges for a better understanding of the function.

(v) If you insert a new column in your already existing table, you might break the function, and it will show an error.

(vi) Numbers that are entered in the table as a text can create an error in the function result.

(vii) Vlookup can only work with single criteria.

(viii) Lastly, it can only work from left-to-right.

Conclusion

Vlookup in excel is one of the most commonly used excel functions and to master the excel. You need to know its in and out completely.

Although excel has come up with its better version, people still consider Vlookup function in excel is still considered to be a better option than others. We hope this article has solved your problem of how to use vlookup in excel & enhance your data analytics skills.

Download Detailed Curriculum and Get Complimentary access to Orientation Session

Date: 28th Nov, 2020 (Sat)
Time: 11:00 AM to 12:30 PM (IST/GMT +5:30)
  • This field is for validation purposes and should be left unchanged.

You may also enroll in a Data Analytics Course for more lucrative career options in Data Science & know how to become a certified data analyst.

Register for FREE Orientation Class on Data Science & Analytics for Career Growth

Date: 28th Nov, 2020 (Sat)
Time: 11:00 AM to 12:30 PM (IST/GMT +5:30)

  • This field is for validation purposes and should be left unchanged.

You May Also Like…

Linear Programming and its Uses

Linear Programming and its Uses

Optimization is the new need of the hour. Everything in this world revolves around the concept of optimization.  It...

An overview of Anomaly Detection

An overview of Anomaly Detection

Companies produce massive amounts of data every day. If this data is processed correctly, it can help the business to...

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *