- Vlookup Excel
- Excel Dlookup In Vba
- Excel Vlookup Example
- How To Use Lookup Function In Excel
- Dlookup Excel
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.
- Placing the VLOOKUP formula. Most people just put formulas into random cells.
- The LOOKUP function in Excel is used to look up information in a row or column. There are two ways to use a LOOKUP formula, depending on your needs: as a vector and an array. The vector type searches only one row or column, while an array searches multiple rows and columns.
With the help of Excel's Lookup functions, you can build a worksheet that will enable you to search a database table. Mary Ann Richardson steps you through the process.
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.
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:
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
Now let’s move to some practical examples of using VLookUp in VBA codes.
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.
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.
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).
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.
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>).
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.
So, this was all about VBA VLOOKUP .
This MSAccess tutorial explains how to use the Access DLookup function with syntax and examples.
Excel Dlookup In Vba
The Microsoft Access DLookup function returns a value from an Access table (or domain).
The syntax for the DLookup function varies depending on what datatype you are uisng in the last parameter. Below we show how to use the DLookup function with numeric, string, and date value in the final parameter.
TIP: Notice the single apostrophe before and after the string value.
TIP: Notice the # symbol before and after the date value.
Parameters or Arguments
- A field, calculation, control on a form, or function that you wish to return.
- The set of records. This can be a table or a query name.
- Optional. It is the WHERE clause to apply to the TableName.
The DLookup function returns any datatype such as a string, numeric, date, etc.
The DLookup function can be used in the following versions of Microsoft Access:
- Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000
Let's look at how to use the DLookup function in MS Access:
Excel Vlookup Example
In this example, you would be retrieving the UnitPrice field from the Order Details table where the OrderID is 10248. This is the same as the following SQL statement:
You can also retrieve a calculation using the DLookup function. For example:
This example would return the UnitPrice field multiplied by the Quantity field from the Order Details table where the OrderID is 10248. This is the same as the following SQL statement:
You could also use a form control in the DLookup function. For example:
This example would return the CustomerID field from the Orders table for the record that is currently being displayed in the Orders form (based on OrderID).
How To Use Lookup Function In Excel
Example in VBA Code
The DLookup function can be used in VBA code in Microsoft Access.
In this example, the variable called LDate would now contain the OrderDate value from the Orders table where the OrderID is 10248.
Example in SQL/Queries
You can also use the DLookup function in a query in Microsoft Access.
In this query, we have used the DLookup function as follows:
This query will return the OrderDate from the Order Details table where the OrderID is equal to 10248. The results will be displayed in a column called Expr1. You can replace Expr1 with a column name that is more meaningful.
The results would now be displayed in a column called OrderDateValue.