The cell G4 will contain the salary of the employee after you run the VBA VLOOKUP code. This will return the value (output of the Vlookup function) in the cell G4.
#Creating vlookup in excel 2016 code
Now, you need to write a VBA VLOOKUP code such that given the name of the employee in a cell, F4, the salary of the employee will be returned in the cell G4. Suppose you have the data on the names of employees and their salary. You will notice that a message box will appear in the Excel sheet. MsgBox “Student with ID:” & student_id & ” obtained ” & marks & ” marks”Ī student with ID:11004 obtained 85 marks. In order to print the marks in a message box, let us use the following command: Since our data is present in the cells B4:D8, we define a range- myrange as:įinally, we input the VLOOKUP function using the Worksheet function in a variable, marks as: Next, we define the range in which the value and the return value exist. Marks = (student_id, myrange, 3, False)įirstly, define a student id, which is the value to lookup.
#Creating vlookup in excel 2016 how to
In VBA code, the VLOOKUP function can be used as:Ī(lookup_value, table_array, col_index_num, range_lookup) How to Use VLookup in Excel VBA?īelow are some examples of the VLookup Code in Excel VBA. In which, lookup_value is the value to look for, table_arrray is the table, col_index_num is the column number of the return value, range_lookup signifies if the match is exact or approximate.
![creating vlookup in excel 2016 creating vlookup in excel 2016](https://i.ytimg.com/vi/D5D7dnBfL8Y/maxresdefault.jpg)
The VLOOKUP function in Excel has the following syntax: The function is not built-in VBA and thus can only call using the worksheet. The worksheet function VLOOKUP can be used in VBA coding. It is also required to mention whether to look for an exact match or an approximate match. The value to search should be present in the first column. Read more is used to search a value in an array and return its corresponding value from another column. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. A unique identifier is uniquely associated with all the records of the database. The VLOOKUP function in Excel VLOOKUP Function In Excel The VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier.
![creating vlookup in excel 2016 creating vlookup in excel 2016](https://i.ytimg.com/vi/0LRbvZS705w/maxresdefault.jpg)
Vlookup is a worksheet function in excel but it can also be used in VBA, the functionality of Vlookup is similar to the functionality in VBA and in worksheet both, as it is a worksheet function the method to use Vlookup in VBA is through Application.WorksheetFunction method and the arguments remain the same.