In this article, we will cover VLookup. To follow along with us make sure you have a copy of this file downloaded using the button below. There are three worksheets, “See it”, “Do it”, and “Data”. “See it” is where I will demonstrate, “Do it” is where you will practice, and “Data” is where our data is living for us to look up the values.
How to use vlookup in excel
Let’s begin! Before we do anything let me explain what VLookup is and how does VLookup works. VLookup is a great function that will help you to look for certain values in any given range of excel. For example, we can pick up value, go look for that value in a certain table and bring in adjacent column values.
Excel Database Free Download
In case you need some data to practice more with the functions in Excel, feel free to download the Free Excel Database .xlsx file using the button below:
How to put vlookup in excel
VLookup takes four arguments:
- The first one is what is that you want Excel to look for?
- Second, where do you want Excel to look for that value?
- Third, what should be returned if the value is found?
- And, finally, should it be exact or an approximate match?
How to apply vlookup in excel
Let us take a close look. We want a department name for all these employees [see the attached workbook for data]. All those employees have come from the datasheet of the workbook used in the demonstration. So let me go to the “See it” sheet and hold my position in B2 cell and, start typing in VLookup. As soon as we see the function press TAB on the keyboard to complete that function. Now, we want Excel to look for value in A2. Where does excel look for that value? Which table? Let us navigate to the “Data” sheet and click on the first column A and holding the left key of your mouse down, drag it across till the department column. So from the First Column till the last column in our selection what’s the column number A-B-C-D-E? That’s the fifth column; so we want Excel to return the fifth column value if the value is found within the First Column. We want it to be the exact match, so I enter ‘0’ and close the bracket and press enter on my keyboard. Our vLookup function will look as follows.
=VLOOKUP(A2, Data!A:E, 5, 0)
As soon as we press enter you should see the department name fetched. To find the department names of other employee IDs, all we have to do is drag the function down using the drag handle on the lower right side.
How to use VLookup formula – Video Demonstration
How to Use VLOOKUP in Excel video tutorial will give you a video walkthrough on how to use vLookup.
Click on the following button to download the file used in the demo to practice along with us.
Excel Learning Series – TOC
- Excel 2016 – Basics – Find Sum, Average, Min and Max
- Excel 2016 Difference Between Formulas and Functions
- Excel 2016 Copy Formulas or Functions
- Microsoft Excel Auto Fill
- Excel 2016 Manage Views using Freeze Panes
- Microsoft Excel – Saving Workbooks
- Excel Upper and Lower Text Functions
- Excel 2016 – Text Function – Proper
- Excel Text Functions – Trim Function
- Excel – Text Function Left Function
- Excel Text Function – Right Function
- Excel Text Functions – MID Function
- Excel Concatenate Text Function
- Excel Nested Functions
- Excel Search Function
- Excel – Calculate Age using Date Function Today
- Calculate Tenure in Excel with Days Function
- Autofill Dates in Excel
- Formatting Dates in Excel
- Excel Date Format Shortcut
- Calculating Sum of Time in Excel
- Calculate Expiry Date in Excel 2016 – EDATE
- Find Age or Tenure using DATEDIF in Excel 2016
- RandBetween – Generate Random Dates in Excel 2016
- Excel Weekday Function
- Excel WeekNum and ISOWeekNum Functions
- Excel Networkdays and Excel Networkdays.INTL
- Excel Workday and Workdays INTL Functions
- Excel DATEVALUE AND TIMEVALUE
- Calculate Time Difference in Excel
- Calculate Quarter in Excel
- Rounding Day, Hour and Minutes in Excel
- Excel Pay Rate and Downtime Cost Calculation
- Excel Logical Functions IF, AND, OR
- Calculate Bonus in Excel Using IF Function
- Excel Learning – SumIF and SumIFS
- How to use Excel Count Functions
- How to use VLOOKUP in Excel
- How to vLookup in a different workbook in Excel
Conclusion – How VLookup Works
We want you to practice as much as you can in order to retain the learning. Go to the “Do it” sheet and try to find out the values for first name, last name, full name, Department, Start Date, and Country. Use the Employee ID as the key and lookup inside “Datasheet”. Make sure that you practice as much as you can. In the next episode, we will cover more dimensions of VLookup. This was how to do VLookup in the same workbook, in the same worksheet. But how do we do it from different workbooks, from different worksheets?