Greeting to all the readers. This article is about the most used function in database “THE VLOOKUP”. Let’s get started 🙂
In this article we will start with the very beginning of the vlookup. Taking the learning ride from syntax to all the complex case studies.
- Understanding the Vlookup
- Syntax of Vlookup
- Locking Rows and Columns
- Ways of using Vlookup
- Using across sheets
- Using in Different workbooks
- Using VBA
Understanding the Vlookup
Excel Vlookup function is part of lookup function category. The “V” stands for Vertical which means it make the search process in Vertical order. Vlookup is basically used when we need to find a value corresponding to a reference value in the table of data in the same row.
One thing to note here is in Vlookup function the reference value must be a unique identifier and should be in the first column of the data in which the search is to be made. It means the Vlookup only looks for the value from left to right direction but in the latest version we have got this crack in the form of Xlookup which works in both the directions. If the Vlookup does’nt find the values we are looking for it will return the error #N/A. Let’s check out some more interesting features of Vlookup.
Following is the syntax of the vlookup –
Here we have four parameters –
- Lookup_value – This is the unique identifier, the reference value, the value which is to be looked upon in the table.
- Table array – The table in which the looup_value is to be located.
- Col_index_num – the column number in the table array which contains the data we need.
[range_lookup] – One thing is to be noticed is the range_lookup is written in square bracket. Whenever any parameter is written in square bracket that value is optional for the formula.
- User can skip that parameter. But in order to specify range lookup has another two sub parameters i.e. True and False.
If True is selected – Excel will look for Approximate match
If false is selected – Excel will look for Exact match.
Let us take an example to have a better understanding
Following image contains data of a bank in which Account number, Name of the customer and Balance in the account is given. Let’s make a vlookup example out of it. We will get the balance of the account given Account number and name of the customer as reference value –
In first example reference value is in cell A3 table array is E1:G14 and column index is 3 i.e balance column. Similarly in second example reference value is in Cell A6 and table array will be from Cell F1:G14. As name of customer is reference value and it should be in first column of the table so we have started the table array from column F.
While copying the formula to multiple cells do take care of locking the cells for table array as excel will adjust the cells reference. Here’s how we can do that –
When we have to lock the cells in the Excel we just put “$” sign and now when we copy paste the formula Excel will not change the reference.
For locking both column and cell we need to put Dollar sign both in front of Column and row. Ex. $A$6
For locking only Column – $A5. Now when we copy paste Excel will move the row number but not the Column
For Locking Row – A$5 put the dollar sign in front of row
Ways of using Vlookup
There are several ways of using Vlookup in Excel i.e.
- Using across the sheets
- In multiple workbooks
- Using VBA.
Using Vlookup in Different Sheets
In real world Vlookup is mostly used when the data is spread across the sheets and workbooks. Let’s discuss one by one the different ways of using Vlookup. The first one in the series is using across the sheets. Keeping all other thing same the only thing that is to be done is when we have to select the table array just simply select the sheet and then select the table.
Using Vlookup in Different Excel workbooks
Following the same steps as above us can use the Vlookup –
- Open both the files in which the table array is and the one in which vlookup is to be applied.
- Start typing the formula and after giving the lookup value when table array is to be given as input, select the file which contains the table array and select it from the specific sheet.
- Continue typing the formula as usual and press Enter
- Once the formula is completed and you closed the file containing the table array the formula will show you the complete path of the file.
Using Vlookup in VBA
For Using VBA we need VBA editor which provides us the work space for typing the code. For activating VBA editor follow these steps –
- Select the Developer Tab in the Ribbon. If Developer Tab is not present in your Ribbon then Right click on the ribbon – > Customize the Ribbon -> then check mark the Developer Tab in the Main Tabs.
- Then Select Visual Basic and Visual Basic Editor will open.
- On the left side of the screen we will have project explorer that will show list of all sheets in the Excel file.
- Right click on the This Workbook —>Insert —> Module
Then just create a sub procedure with the requirements and the VBA Code for Vlookup is –
WorksheetFunction.vlookup(LookupValue, TableArray, ColumnIndex, False)
Submit your review
it was simple and helpful
Thank you for your review