Microsoft Excel is one of the most widely used spreadsheet software in the world. Excel allows us to write codes and create macros to automate our tasks. VLookup function is the go-to function for quickly finding outputs based on specific data in a large dataset in Excel VBA. But did you know that Excel VBA allows you to use named ranges with VLookup? Using named ranges can keep our formula more readable, and less prone to error at the same time. In this article, we will learn to use the VBA VLookup function with a named range in Excel by discussing different examples.
Here is a brief video describing one of the things that you will be capable of doing after going through this article. To obtain a general grasp of this article, you can check this video.
Overview of VBA VLookup and Named Range
Before diving into the main article, let’s familiarize ourselves with the key elements of this article. First, let’s discuss the VBA VLookup function of Excel.
Summary of VBA VLookup Function
The VBA VLookup function finds a specified value from the first column of a defined table array and returns a value from another column of the same table array.
Syntax
The syntax of the VBA VLookup function is given below.
WorksheetFunction.VLookup(Arg1, Arg2, Arg3, [Arg4])
Here, the expression indicates a variable that refers to a Worksheet Function object.
Arguments
| Arguments | Required/Optional | Explanation |
|---|---|---|
| Arg1 | Required | It refers to the value which we will search in the first column of the lookup_array. This is analogous to the lookup_value of the VLOOKUP function in the Excel worksheet. |
| Arg2 | Required | It indicates the range of the cells that contain the data. It is analogous to the table_array argument of the VLOOKUP function in the Excel worksheet. |
| Arg3 | Required | This represents the column number of the output column in the table_array. This argument is analogous to the col_index_no argument of the VLOOKUP function. |
| Arg4 | Optional | It denotes a logical value (TRUE/FALSE). TRUE value is applicable when we search for an approximate match of Arg1. On the other hand, the FALSE value is used for searching for an exact match of Arg1. This argument is analogous to the [range_lookup] argument of the worksheet VLOOKUP function. |
Named Range
A named range is a collection of one or more cells that have given a name to it. We can use this name in formulas to indicate that range. Properly using the named range can make our formula more understandable and user-friendly.
For instance, let’s say we have the Employee Details of X Company as our dataset. We will use this dataset to demonstrate how we can create a named range most easily.
- Select the entire range >> click on the Name Box.
- After that, type in the name of the named range. Here, we have used the Employee_Data as our named range. Finally, press ENTER from your keyboard.
That’s it! You have successfully created a named range. In this article, we will use this named range with the VBA VLookup function in the upcoming examples.
How to Launch VBA Editor in Excel
Now, let’s learn about launching the VB Editor in Excel. Launching the VB Editor will allow us to write VBA code in Excel. So, it’s essential to know before proceeding any further. To launch VB Editor you need to follow the instructions given below.
- Go to the Developer tab from Ribbon >> click on the Visual Basic option.
Note: You need to have the Developer option activated in your Excel application. If it is not available, then you can manually enable the Developer option.
- Go to the Insert tab >> Select the Module option.
This will create a blank Module. You can write your code in this Module. In this article, we will create separate Modules for each code.
Note: You can double-click on any sheet name in the VB Editor and write code there. However, the codes that are written in the sheets will only work in those particular sheets. On the other hand, codes that are written in a Module will work on any sheet of that particular workbook.
Excel VBA VLookup with Named Range: 5 Examples
In this section of the article, we will discuss five practical examples of using the VBA VLookup function with a named range in Excel. So, let’s start this article and explore these examples.
1. Showing Single Output for Definite Entry from Named Range in MsgBox
In the first example, we are going to demonstrate the VBA VLookup function with a named range and show the output with the help of a MsgBox. This method will come in handy if you are looking for ways to display a single output after a search by the VBA VLookup function in Excel. Here, we will use the named range that we created earlier (Employee_Data).
The following video demonstrates an overview of this example.
Now, let’s follow the steps mentioned below to learn the detailed procedure.
Steps:
- Create a blank Module >> Write the following code >> click on the Save icon.
Sub VLOOKUP_Single_Output()
Dim myRange As Range
Dim lookup_value As Variant
On Error GoTo errorMsg
Set myRange = Range("Employee_Data")
lookup_value = Range("B23")
MsgBox WorksheetFunction.VLookup(lookup_value, myRange, _
3, False), , "Employee Name"
Exit Sub
errorMsg:
MsgBox "Please use a valid Employee ID"
End SubCode Breakdown
List of Variables Used
| Variable Name | Data Type |
|---|---|
| myRange | Range |
| lookup_value | Variant |
On Error GoTo errorMsg- If any error occurs in the code then the On Error statement will start executing the code from the errorMsg block and skip all the lines of codes in between.
Set myRange = Range("Employee_Data")
lookup_value = Range("B23")- The Set statement is used to define the range of the data (Employee_Data).
- After that, the value of cell B23 is assigned as lookup_value.
MsgBox WorksheetFunction.VLookup(lookup_value, myRange, _
3, False), , "Employee Name"
Exit Sub- The WorksheetFunction.VLookup method matches the first column of the named range with the lookup_value and returns values from the 3rd column of the named range if it finds an exact match.
- Then, we used the MsgBox function of Excel VBA to show this output in a MsgBox. Here, the title of the MsgBox is “Employee Name”.
- After executing this line, the code will exit the sub-procedure.
errorMsg:
MsgBox "Please use a valid Employee ID"
End Sub- Generally, an error will occur here if there is no match found against the Employee ID. So, it will show a MsgBox that says “Please use a valid Employee ID” and our subprocedure ends here.
- Now, use the keyboard shortcut key ALT + F11 to return to the worksheet.
At this stage, your code is ready to run. In Excel, there are numerous ways to run a VBA code. In this article, we will use these different ways. You can use any of the ways according to your preference.
Running Macro Using Developer Tab:
- First, insert a valid Employee ID in cell B23. In this example, we have used Employee ID 1100809. After that, go to the Developer tab and choose the Macros option.
Note: If you enter an incorrect Employee ID, the code will return a warning. We will discuss this later in this example. It is to be noted that, this note does not apply not only to this example but also to all the examples in this article.
- The Macro dialogue box will then show up on your worksheet. From there select the VLOOKUP_Single_Output option and click on Run.
As a result, you will have the Employee Name against the Employee ID 1100809 displayed in an MsgBox as shown in the following image.
Now, let’s see what happens if you enter the wrong Employee ID. As you can see, we have used the wrong Employee ID in cell B23. Then, run the VBA code again.
We are getting a MsgBox with a message that says “Please use a Valid Employee ID”.
If you ask, how is this code understanding that it is indeed an incorrect Employee ID? The answer is simpler than you think. Any Employee ID that is not available in the first column of our specified range will be considered an invalid Employee ID.
So, be careful while entering your Employee ID. In the next examples, we will use a better way to insert Employee ID.
Read More: Excel VBA Vlookup with Multiple Criteria
2. VLookup Values from a Named Range with User Input
In the previous example, we entered Employee ID directly and we needed to be very precise while entering the ID. In this example, we will use a more convenient way to insert an Employee ID.
Moreover, we will select a cell and insert the Employee ID in a pop-up dialogue box that will appear after running the code. Whereas, in the previous example, we had to write the Employee ID in a definite cell in the worksheet before running the code.
The following video indicates an overview of this example. You can check this out to get an overall idea about what we will be doing in this example. Here, we will use the same named range as the previous example.
To get a detailed idea about the procedure, let’s follow the instructions outlined below.
Steps:
- Create a Module >> Type the following code >> click on the Save icon.
Sub VLOOKUP_Single_Output()
Dim myRange As Range
Dim lookup_value As Variant
On Error GoTo errorMsg
Set myRange = Range("Employee_Data")
lookup_value = Range("B23")
MsgBox WorksheetFunction.VLookup(lookup_value, myRange, _
3, False), , "Employee Name"
Exit Sub
errorMsg:
MsgBox "Please use a valid Employee ID"
End SubCode Breakdown
List of Variables Used
| Variable Name | Data Type |
|---|---|
| myRange | Range |
| lookup_value | Variant |
lookup_value = Int(Application.InputBox _
("Please enter an Employee ID", "Employee ID", , , , , , 8))- The Application.InputBox method takes input from a user and assigns it to the lookup_value variable.
- “Please enter an Employee ID” is the message that will show in the InputBox after opening. Here, 8 indicates the Type parameter of the Application.InputBox method.
MsgBox WorksheetFunction.VLookup(lookup_value, myRange, 3, _
False), , "Employee Name"- Here, we used a MsgBox to show the output after using the VBA VLookup function.
- After saving the code, use the keyboard shortcut ALT + F11 to return to the worksheet.
In this example, we will run the code using the Macro dialogue box like in the previous example. But here, we will use a keyboard shortcut to open the Macro dialogue box rather than using the Developer tab.
Running Macro Applying Keyboard Shortcut:
- To open the Macro dialogue box, use the keyboard shortcut ALT + F8. Now, from the Macro dialogue box select the VLOOKUP_User_Input option and click on Run.
A dialogue box named Employee ID will open on your worksheet. In this dialogue box, you don’t need to insert an Employee ID. Instead, just select the cell that contains the Employee ID. This is a more convenient and error-free approach.
- Click on the Employee ID dialogue box >> choose any cell from the Employee ID column >> click OK.
The Employee Name for the selected Employee ID will be available on your worksheet in an MSgBox as demonstrated in the image below.
3. Extracting All Columns Data for Definite Entry from Named Range
In the previous two examples, we have displayed only the Employee Name based on the provided Employee ID. But if you want to have all the details of an employee instead of only the name of the employee then this will be a perfect example for you. We have used the same named range here as in the earlier examples.
Here, we have a video that demonstrates an overview of the steps and outcome of this example.
Now, if you are interested in learning the procedure to extract all columns’ data step by step then use the instructions mentioned below.
Steps:
- Create a Module >> Write the VBA code >> Click on the Save icon.
In this example, we will run the VBA code by using the Run Sub/Userform option from the VB Editor window.
- Next, click on the Run Sub/Userform option in the VB Editor window.
Sub VLOOKUP_Single_Output()
Dim myRange As Range
Dim lookup_value As Variant
On Error GoTo errorMsg
Set myRange = Range("Employee_Data")
lookup_value = Range("B23")
MsgBox WorksheetFunction.VLookup(lookup_value, myRange, _
3, False), , "Employee Name"
Exit Sub
errorMsg:
MsgBox "Please use a valid Employee ID"
End SubCode Breakdown
List of Variables Used
| Variable Name | Data Type |
|---|---|
| myRange | Range |
| lookup_value | Variant |
| end_row | Long |
| i | Long |
| result | String |
| Arr | Variant |
ReDim Arr(3)- Here, we applied the ReDim statement to specify the size of the array. Here, the array size is 3.
For i = 2 To 5
Arr(i - 2) = Cells(4, i).Value
Next j- A For Next loop stores column headers of row 4 inside the array by looping through column B to column E.
For i = 2 To 5
If i = 3 Then
result = result & Arr(i - 2) & ": " & _
Format(WorksheetFunction.VLookup(lookup_value, _
myRange, i - 1, False), "m/d/yyyy") & vbCrLf
GoTo nextLoop
ElseIf i = 5 Then
result = result & Arr(i - 2) & ": " & _
Format(WorksheetFunction.VLookup(lookup_value, _
myRange, i - 1, False), "$#,###") & vbCrLf
GoTo nextLoop
End If
result = result & Arr(i - 2) & ": " & _
WorksheetFunction.VLookup(lookup_value _
, myRange, i - 1, False) & vbCrLf
nextLoop:
Next i- A For Next loop will loop through column B to column E.
- The VBA If Statement will check whether the current column is column C or not. If it is column C then the WorksheetFunction.VLookup method will be used and format the output as a date.
- After that, we concatenated this output with the respective column headers from row 4 and added the vbCrLf constant of VBA to insert a line break. Then, we assigned this to the result variable.
- We jumped to the next iteration by going to the nextLoop block using the GoTo statement. So, if the current column is column C, this part of the For Next loop will execute.
- ElseIf statement is used to check whether the current column is column E or not. If it is column E, then the output will be in currency format. The rest of the part is similar to the output of the previous If statement.
- If the current column is neither column B nor column E, then no formatting will be applied.
Consequently, the following dialogue box named Employee ID will open on your worksheet to enter the cell containing an Employee ID.
- Click on the dialogue box >> select any cell from the Employee ID column >> click OK.
As a result, all the data of all columns against the selected Employee ID will be available to you in a MsgBox as shown in the following picture.
Read More: How to Use Excel VBA VLookup Within Loop
4. Adding Multiple Columns from Different Named Ranges with Matched Columns
This example demonstrates how we can extract multiple columns of data from a different named range. Here, we will discuss two different ways to achieve this. Moreover, we will learn to use a button to run our code. This gives a sleek and clean look to your worksheet.
Now, let’s learn about these methods to add multiple columns from different named ranges using the VBA VLookup function in Excel.
4.1. Using Only VLookup Result
In the first method, we will use the default VBA VLookup function provided by Excel. The following video describes an overview of this method. You can check this to have an overall idea about the method.
Steps:
- We need to create a named range with a range of B12:D18. We named it “New_Data”. Then, create a Module >> write the VBA code >> click on the Save icon to save the code.
Sub VLOOKUP_Add_multipleColumns()
Dim myRange As Range
Dim lookup_value As Variant, end_row As Long, i As Long, j As Long
Dim result As Variant
Set myRange = Range("New_Data")
On Error GoTo errorMsg
For i = 4 To 10
lookup_value = Cells(i, 2)
result = xlempty
For j = 3 To 4
result = WorksheetFunction.VLookup(lookup_value, myRange, j - 1, False)
Cells(i, j + 3).Value = result
Cells(i, j).Copy
Cells(i, j + 3).PasteSpecial xlPasteFormats
If i = 4 Then
Cells(i, j + 3).Columns.AutoFit
End If
Application.CutCopyMode = False
Next j
Next i
Range("B2:G2").Merge
Range("B2:G2").Style = "Heading 2"
Exit Sub
errorMsg:
MsgBox "Please use a valid Employee ID"
End SubCode Breakdown
List of Variables Used
| Variable Name | Data Type |
|---|---|
| myRange | Range |
| lookup_value | Variant |
| end_row | Long |
| i | Long |
| j | Long |
| result | Variant |
For i = 4 To 10
lookup_value = Cells(i, 2)
result = xlempty
For j = 3 To 4
result = WorksheetFunction.VLookup(lookup_value, myRange, j - 1, False)
Cells(i, j + 3).Value = result
Cells(i, j).Copy
Cells(i, j + 3).PasteSpecial xlPasteFormats
If i = 4 Then
Cells(i, j + 3).Columns.AutoFit
End If
Application.CutCopyMode = False
Next j
Next i- The outer loop will loop through the rows of the output range. In this case, it is from row 4 to row 10. On the other hand, the inner For Next loop will loop through the 2nd and 3rd columns of the “New_Data” range.
- Then, we assigned the cells of column B of the output range as the lookup_value.
- After that, a VLookup operation is used for each column using the index number “j-1,” the lookup_value variable, and the range “myRange“. The “result” variable receives the lookup’s outcome as its value.
- Then, we assigned the value of the result variable in cell(i,j+3) which refers to cell F4 in the worksheet.
- Following that copied a cell from the current row and pasted the formatting in the cell where the value of the result variable is just stored.
- Next, we used an If statement to check whether the current row is row 4 or not. If it is row 4, then we auto-fitted the column width by using the Range.AutoFit method and the Application.CutCopyMode property is set to False.
Range("B2:G2").Merge
Range("B2:G2").Style = "Heading 2"
Exit Sub- Here, we merged the cells of the range B2:G2 by using the Range.Merge method. Then, we specified the Cell Style of the merged range to “Heading 2”. After that, the code will exit the sub-procedure.
At this stage of the article, we will learn how we can create a button to run a specific VBA code in Excel.
Running Code by Button:
- Go to the Developer >> select the Insert option from the Controls group >> click on the Button (From Control) option.
- Then, draw the shape of the button as shown in the picture below.
- As soon as you finish drawing the button, the Assign Macro dialogue box will appear on your worksheet, and select the VLOOKUP_Add_multipleColumns option >> click OK.
- After that, click on the button to rename it. In this case, we have renamed it as “Load New Data”.
- Following that, right-click on the button to format the font of the button name and select the Format Control option.
As a result, the Format Control dialogue box will open on your worksheet.
- Choose your preferred font in the Font section >> select Bold in the Font Style section >> define the font size >> click OK.
At this stage, your button is ready to use and your button will look somewhat like the button marked in the following image.
- After that, click on the button to run the code.
Consequently, the Region and Department columns from the named range “New_Data” will be added automatically to the original dataset.
Note: Here the Employee IDs of the “New_Data” range are not in sequential order. However, after running the code, the Region and Department columns will be added sequentially to the output dataset.
4.2. Using Worksheet Function in VBA
In Excel VBA, we can also use the worksheet functions of Excel. In this method, we will demonstrate this with the VLOOKUP function with a named range. Here, we used the same named range as in the previous example (“New_Data”). This video describes an overview of this method.
Now, let’s follow the steps mentioned below.
Steps:
- Firstly, create a Module >> write the following VBA code >> click on the Save icon.
Sub VLOOKUP_Add_multipleColumns()
Dim myRange As Range
Dim lookup_value As Variant, end_row As Long, i As Long, j As Long
Dim result As Variant
Set myRange = Range("New_Data")
On Error GoTo errorMsg
For i = 4 To 10
lookup_value = Cells(i, 2)
result = xlempty
For j = 3 To 4
result = WorksheetFunction.VLookup(lookup_value, myRange, j - 1, False)
Cells(i, j + 3).Value = result
Cells(i, j).Copy
Cells(i, j + 3).PasteSpecial xlPasteFormats
If i = 4 Then
Cells(i, j + 3).Columns.AutoFit
End If
Application.CutCopyMode = False
Next j
Next i
Range("B2:G2").Merge
Range("B2:G2").Style = "Heading 2"
Exit Sub
errorMsg:
MsgBox "Please use a valid Employee ID"
End SubCode Breakdown
List of Variables Used
| Variable Name | Data Type |
|---|---|
| myRange | Range |
Range("F4").Value = "=VLOOKUP($B4,New_Data,COLUMN(B$12),FALSE)"
Range("F4").AutoFill Range("F4:F10")
Range("F4").AutoFill Range("F4:G4")
Range("F4").AutoFill Range("F4:G10")- Here, the VLOOKUP function extracts values from a different named range (“New_Data) and the result is assigned to cell F4.
- Then, we used the Range.AutoFill method to copy this formula up to cell F10.
- Following that, we copied the formula in a horizontal direction up to the next cell and after that, we copied the formula up to cell G10 using Range.AutoFill method.
Range("C12:C18").Copy
Range("F4:F10").PasteSpecial xlPasteFormats
Range("F4").Columns.AutoFit
Range("D12:D18").Copy
Range("G4:G10").PasteSpecial xlPasteFormats
Range("G4").Columns.AutoFit- Next, we copied the formatting of the 2nd and 3rd columns of the range “New_Data” and pasted the formatting in column F and column G respectively.
- Here, we pasted the formatting by applying the Range.PasteSpecial method. The xlPasteFormats parameter specifies that only the formatting should be pasted, not the cell contents.
- We also auto-fitted the column width of column F and column G concerning their headers using the Range.AutoFit method.
Now, create a button and assign the macro named VLOOKUP_Excel_Formula to the button by following the steps mentioned in the earlier method.
- After that, click on the button to run the code.
As a result, the Region and the Department columns from the “New_Data” range will be added as demonstrated in the following picture.
Read More: Excel VBA to Vlookup Values for Multiple Matches
5. Creating a User-Defined Function to Apply VBA VLookup Function with Named Range
Creating a VBA user-defined function is another smart way to apply the VBA VLookup function with a named range in Excel. In this example, we will explain the detailed steps to create this custom function. Here, we have used the named range from the first example of this article. The following video indicates the summary of this example. You can watch this to get an overall idea about this example.
Steps:
- Create a Module and write the VBA code >> click on the Save option.
Function VBA_Vlookup(lookup_value As Variant, _
lookup_array As Range, col_index_no As Long, _
Optional match_case As Boolean = False) As Variant
VBA_Vlookup = WorksheetFunction.VLookup(lookup_value, _
lookup_array, col_index_no, match_case)
End FunctionCode Breakdown
Function VBA_Vlookup(lookup_value As Variant, lookup_array As Range, col_index_no As Long, Optional match_case As Boolean = False) As Variant- First, we created a function named VBA_Vlookup with the following arguments. We set the data type of the return value of the function as Variant.
| Arguments | Required/Optional | Explanation |
|---|---|---|
| lookup_value | Required | This is the value that is required to be found in the first column of the lookup_array range. |
| lookup_array | Required | This indicates the range of cells where we will search for lookup_value and extract the output values. |
| col_index_no | Required | This represents the column number relative to the lookup_array range from where we want to extract the outputs. |
| match_case | Optional | This defines whether we want an exact match or an approximate match. |
VBA_Vlookup = WorksheetFunction.VLookup(lookup_value, lookup_array, col_index_no, match_case)- Here, we used the WorksheetFunction.VLookup method to perform the VLookup operation and assign the output in the VBA_Vlookup function.
- Finally, we ended the function.
- After that, insert the following formula in cell C23 >> press ENTER
=VBA_Vlookup(B23,Employee_Data,3,FALSE)
Here, cell B23 represents the Employee ID which is the lookup_value argument in our custom function, Employee_Data is the named range that defined the lookup_array argument, 3 indicates the number of columns of the lookup_array from where we want to return the output, and FALSE indicates that we are looking for an exact match here.
Subsequently, you will get the Employee Name according to the Employee ID used.
I hope this example has provided you with great knowledge on how you can Vlookup with a named range in Excel VBA.
How to Use VBA VLookup Function with Named Range in Another Workbook in Excel
In this section of the article, we will apply the VBA VLookup function to extract data from a different workbook. In the following section, we have an overview video about this method and its output.
In this article, we have provided two Excel workbooks. The workbook named “Sample Dataset” contains the following range of data. Here, we named this range of data B4:E20 as “Another_WB”. We will use this named range in this method.
Now, let’s follow the instructions outlined below to use the VBA VLookup function with a named range to extract data from a different workbook.
Steps:
- Create a new Module in your original workbook (not in the “Sample Dataset” workbook) >> Write the VBA code >> click on the Save option.
Sub Another_Workbook()
Dim myRange As Range
Dim lookup_value As Variant
Dim strFile As String
On Error GoTo errorMsg
strFile = Application.GetOpenFilename()
Workbooks.Open strFile
Set myRange = Range("Another_WB")
Workbooks("Using_VBA_VLOOKUP_with_Named_Range.xlsm").Activate
lookup_value = Range("B23")
Range("C23").Value = WorksheetFunction.VLookup(lookup_value, _
myRange, 3, False)
Workbooks("Sample Workbook.xlsx").Activate
ActiveWorkbook.Close
Exit Sub
errorMsg:
MsgBox "Please use a valid Employee ID"
End SubCode Breakdown
List of Variables Used
| Variable Name | Data Type |
|---|---|
| myRange | Range |
| lookup_value | Variant |
| strFile | String |
strFile = Application.GetOpenFilename()
Workbooks.Open strFile- After that, we applied the Application.GetOpenFilename method to open a dialogue box so that we can get the name of the file from which we need to extract data.
- Then, we used the Workbooks.Open method to open that Excel file.
Workbooks("Using VBA VLookup with Named Range.xlsm").Activate
lookup_value = Range("B23")
Range("C23").Value =WorksheetFunction.VLookup(lookup_value, _ myRange, 3, False)- Here, we activated the initial workbook by using the Workbook.Activate method.
- Then, we applied the WorksheetFunction.VLookup method to get the data from another workbook and assign the output to cell C23.
Workbooks("Sample Workbook.xlsx").Activate
ActiveWorkbook.Close
Exit Sub- At this stage, the file from which we extracted values is still open. We want to close it. So, we activated it first using the Workbook.Activate method.
- Then, we employed the ActiveWorkbook.Close method to close it.
- After executing this line, the code will exit the sub-routine.
- After saving the code, use the keyboard shortcut ALT + F8 to open the Macro dialogue box. Then, select the Another_Workbook option and click on Run.
As a result, the code will extract the Employee Name from the “Sample Dataset” workbook as demonstrated in the following image.
Here, if you look closely, you will see that the associated Employee Name for Employee ID 1100811 is “Samantha Patel”. But we have “Dylan Patel” in the output cell and this name is not present in this workbook. So, it must have come from another workbook. In this case, it came from the “Sample Dataset” workbook.
How to Use VBA VLookup Function with Table Array in Excel
In the previous methods, we used a named range to apply the VBA VLookup function in Excel. But you can also use a Table Array instead of a named range in the VBA VLookup function. Here we have a table named “MyTable” as our dataset. The following video describes the summary and the output of this method. You can check this out to get an overview of the method.
Now, let’s use the following steps to use the VBA VLookup function with Table Array in Excel.
Steps:
- Create a blank Module >> Write the VBA code >> Click on the Save icon.
Sub VLOOKUP_Table()
Dim Table1 As ListObject
Set Table1 = Worksheets("Table Array").ListObjects("MyTable")
On Error GoTo errorMsg
lookup_value = Range("B23")
Range("C23").Value = WorksheetFunction.VLookup(lookup_value, _
Table1.Range, 3, False)
Exit Sub
errorMsg:
MsgBox "Please use a valid Employee ID"
End SubCode Breakdown
List of Variables Used
| Variable Name | Data Type |
|---|---|
| Table1 | ListObject |
| lookup_value | Variant |
Dim Table1 As ListObject
Set Table1 = Worksheets("Table Array").ListObjects("MyTable")- First, we declared the variables required.
- Then, we used the Set statement to define the table from the worksheet named “Table Array”. Here, we named our table “MyTable” in the worksheet.
Range("C23").Value = WorksheetFunction.VLookup(lookup_value, _
Table1.Range, 3, False)
Exit Sub- Following that, we assigned the value of cell B23 to the lookup_value variable. Then, we applied the WorksheetFunction.VLookup method to extract the data from the specified table and assign the result to cell C23.
- After that, the code will exit the sub-procedure.
- Subsequently, use the keyboard shortcut ALT + F8 to open the Macro dialogue box and click on Run.
The Employee Name against the Employee ID of cell B23 will be available in cell C23.
Things To Remember
- You need to be careful about the syntax and arguments of the VBA VLookup function. Writing incorrect arguments in the code will lead to errors.
- It is important to know that, by default, the InputBox function returns values as Strings. So, it’s better to define the data types of the variables at the start of the code to avoid any unwanted errors.
- You also need to correctly define the named ranges and use the names exactly in the code to indicate that range.
Takeaways from This Article
- Using Excel VBA will allow you to automate the task thus saving tons of time.
- Applying the named ranges will help to simplify the formula which will make the formulas more readable and descriptive.
- VBA VLookup is a useful function for searching data from a large dataset.
- Utilizing the combination of the named ranges and VBA VLookup function in Excel can increase readability and lower the chance of errors in your code as well as automate the job.
Practice Section
In the practice workbook, we have provided a Practice Section. Please practice these examples by yourself in the Practice Section. It will solidify your understanding of using the VBA VLookup function with a named range in Excel.
Download Practice Workbook
You can download our practice workbook from here for free!
Conclusion
In conclusion, using the VBA VLookup with a named range in Excel can allow you to significantly improve the readability, and efficiency of the code. In this article, we have tried to cover different ways to use the VBA VLookup with a named range. If you encounter a problem that can’t be solved by applying the concepts of any of these examples, you can let us know in the comment section of this article. We will get back to you with a solution as soon as we can. You can also let us know if you have any other suggestions about the article.
Further Readings
- Use Excel VBA VLOOKUP to Find Values in Another Worksheet
- Excel VBA to Vlookup in Another Workbook Without Opening
- Excel VBA: Working with If, IsError, and VLookup Together
