One of Excel’s most powerful and flexible tools is the VLookup feature. It’s common to be compelled to code a VLookup macro in Excel VBA as you gain experience with macros. The Excel VLookup function is useful for finding a matching value in another column by searching an array.
One of the most used features in Excel is the Vertical Lookup. The most typical application is to utilise it to access information in another table by specifying a key. VBA code can take advantage of the VLookup worksheet functions. VLookup can also help verify an item exists, find duplicates, and organise values into lists.
Syntax
Application.WorksheetFunction.VLookup(lookup_value, table_array, column_index, range_lookup)
1. Lookup Value
It is the base value you are searching for. This information helps to look up a number in a database.
2. Table Value
This is the table that holds all the values. The information will be retrieved from this table via a lookup value.
3. Column Index Number
The number of columns we are looking for in the table. There could be hundreds of columns in a table, but we only need one—the one containing the information we need.
4. Range Lookup
In this case, we have to say what result we want. If we want a precise match, we should specify that as FALSE or 0, whereas if we want an approximate match, we can specify it as TRUE or 1.
See More: VBA InputBox
How to use VLookup in Excel VBA
Step 1: In the Developer menu, select Visual Basic.
Step 2: Insert a Module by clicking the Insert button.
Step 3: Write the VLookup code.
Step 4: Press the Run button to execute the programme.
Examples
Let’s say we have information of Student marks. Student information is shown in the table below as a pop-up window.
Columns B and C of the following table contain the relevant information. Marks are returned in cell C, Student Roll Numbers in cell A and names in cell B.
Enter the VLookup code and specify the extent to which data exists, such as in columns A, B, and C.
Fill in the students’s name in cell B2:B6 and their marks in C2:C6. Next, use WorksheetFunction to invoke VLookup, and save the result in the Sal cell.
| Sub VLookupFunction_Example()
Dim str_Student_Name As String Dim iMarks As Integer str_Student_Name = “Peter” iMarks = Application.WorksheetFunction.VLookup(str_Student_Name, ThisWorkbook.Worksheets(“Sheet6”).Range(“B2:C6”), 2, False) MsgBox iMarks End Sub |
We used the variable “str_Student_Name” to keep track of the Student Marks. VLookup’s input is the Student’s name, and the output is the corresponding Marks.
Click the Run button to begin running the programme. And the output will look as below.
