- If you know the name of a product, for instance, and you want to quickly determine its price, you can simply enter the product name into Excel and VLOOKUP will find the price for you.
- MS Excel: How to use the LOOKUP Function (WS) Description. The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. LOOKUP Function (Syntax #1). In Syntax #1, the LOOKUP function searches for value in the lookuprange and returns the.
- VLookup is an Excel worksheet function. To use it in VBA try: iStudent = Application,VLookup (.).
- In Excel, use VLOOKUP when you need to find things in a table or range by row. Learn more at the Excel Help Center: highly antic.
In my earlier post, I had written about VLOOKUP in Excel. It was a massive post of around 2500 words, it explains most of the things about the vertical lookup function in excel. Today’s post is an extension to that post and here we will understand how to apply a VLOOKUP in VBA.
If you haven’t read that post then I would strongly recommend you read that post before going any further. [Read Here]
Assuming that you have basic knowledge of the VLOOKUP function we will move further.
The VLOOKUP function in Excel finds things in a table or range by row. The secret is to organize your data so that the value you look up is to the left of the value you.
Note: To perform these programs yourself, you may need to enable macros in excel. Read this post to know how to do this.
Syntax of VBA VLOOKUP
You can use VLookUp in macros by following any of the below ways:
Or
Note: If you are searching for something similar to the VLOOKUP function for Access then probably you should use DLOOKUP.
5 Examples of Using VLOOKUP in VBA
Lookup Excel Exact Match
Now let’s move to some practical examples of using VLookUp in VBA codes.
Example 1
Using VLookUp find the monthly salary of “Justin Jones” from the below table. Display the salary using a dialog box.
Below is the code for this:
Explanation: In this code, we have used a variable ‘E_name’ to store the employee name whose salary is to be fetched. After this, we have simply supplied the employee name and other required arguments to the VLOOKUP and it returns the salary of the corresponding Employee.
Example 2
Now make the above program a little customizable by accepting the Employee name from the user. If the user enters any Employee name that is not present in the table then the program should be able to convey this clearly to the user.
To accomplish this we can use the below code:
Explanation: In this code, we are accepting the user input using an InputBox function. If the Employee name entered by the user is found, then VLookUp returns its corresponding salary. However, if the employee name is not present in the table then VLOOKUP throws a “1004 Error”.
And, we have created an error handler to catch such cases for conveying the user that entered employee name doesn’t exist.
Example 3
In this example we will try to write a code that adds the Department field from the Employee Table 1 to our old Employee Table.
As you can see that in both these tables there is only one common column i.e. Employee_ID. So, in this case, we will have to apply the VLookUp based on the Employee ID.
Below is the code to do this:
Explanation: This code takes each ‘lookup_value’ from the Employee ID field (one at a time), looks up its corresponding Department, and then populates the corresponding department value at the appropriate place.
Please note that in this code we have just pasted the result of the VLOOKUP formula, and not the VLookUp formula itself (Refer Example 5).
Example 4
In this example we will try to write a code that displays all the details of an employee from the Employee table (as shown below) when its Employee ID is entered.
Below is the code that can accomplish this:
Explanation: In this example, we have asked the user to enter the Employee Id and then we have used multiple VLookUp Statements and concatenated their outputs to show all the details in a single message box.
Example 5
Redo example 3 but this time paste the whole VLookUp formula instead of pasting only the result.
Below is the code for doing this:
Explanation: This code is very similar to the one that we have discussed in Example 3, the only difference between these formulas is that here we are copying the VLookUp formula directly in the cells.
In this code, we have applied the VLOOKUP in R1C1 form. So, the formula =VLOOKUP(RC[-4], R3C8:R13C9, 2, False) means =VLOOKUP(<4 cells to the left of current cell>, <Range of Employee Table 1>, <column to be fetched>, <exact match>).
Vlookup Excel
One thing that is worth noting here is: the square brackets ( [ ] ) in your R1C1 formula indicate that you are specifying a relative range. If you want to specify an absolute range, you need to specify the R1C1 cells without brackets; e.g. R3C8:R13C9.
Dlookup Excel
So, this was all about VBA VLOOKUP .