VBA Macro For VLOOKUP From Another Sheet

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data from another worksheet in Excel 2013/2016/2019.

VLOOKUP Function in VBA Macro


You should know that VLOOKUP is a worksheet function in Excel, and it can be used to search a value in an array table and return its corresponding value from another column or from another worksheet in Excel. and the VLOOKUP function can be used in VBA Macro in Excel. The functionality of VLOOKUP function is similar to the functionality in VBA. Both method and the arguments remain the same for VLOOKUP method in VBA code.

VLOOKUP Example in Excel VBA Example


The below will show you one examples of the VLOOKUP function in Excel VBA Macro. And suppose you want to retrieve the number of sales for product “word” in Cell E2 appears in the second column of the table array argument A2:C6. To lookup for the value “word”, and you just need to follow the below steps:

Step 1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View CodeMicrosoft Visual Basic for Applications window pops up.

hide every other row1

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic. You can also press Alt + F11 keys simultaneously to open it.

How to Remove All Extra Spaces and Keep Only One Between Words 5

Step 2: In Microsoft Visual Basic for Applications window, enter below code:

Sub vlookupExample()

    On Error Resume Next

    lookupValue = "word"

    Set myrange = Range("B2:C6")

    result = Application.WorksheetFunction.VLookup(lookupValue, myrange, 2, False)

    MsgBox "the sales number of " & lookupValue & " is " & result

End Sub

vba macro for vlookup from another sheet1

Step 3: Save code, quit Microsoft Visual Basic for Applications.

Step 4: Click Developer->Macros to run Macro.

Highlight All Non-Blank Cells 13

Step 5: Select the Macro Name ‘vlookupExample’ from the Macro window and click Run.

vba macro for vlookup from another sheet1

Step6: let’s see the result:

vba macro for vlookup from another sheet1

Let’s see how this VBA Macro works:

You need to define a lookup value variable, which is the value to lookup.

lookupValue = “word”

Then you need to define the range in which the value and the return value exist. As your array table is B2:C6 in your worksheet, and defining a variable named as “myrange” as below:

Set myrange =Range(“B2:C6”)

Next, you need to call the VLOOKUP function from Application class and its sub-method WorksheetFunction, like below:

Application.WorksheetFunction.VLookup(lookupValue, myrange, 2, False)

Last, you can call MsgBox function to print the result in a message box, so you can use the following line:

MsgBox “the sales number of ” & lookupValue & ” is ” & result

Related Functions


  • Excel VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
Related Posts

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

Get Employee Information by VLOOKUP

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today, in this article, we will show you the way to apply VLOOKUP to retrieve employee information. I hope this article will help you in ...

VLOOKUP with Two Lookup Tables

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today we will show you the application of VLOOKUP function when there are two lookup tables. EXAMPLE Table1 and table2 record the rates of Y2020 ...

VLOOKUP with Multiple Lookup Values

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP Data by Date

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Workbook
VLOOKUP - Retrieve Data from Another Workbook 1

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Worksheet
VLOOKUP - Retrieve Data from Another Worksheet 3

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

Case Sensitive Lookup with SUMPRODUCT and EXACT

Today, we will show you how to use SUMPRODUCT and EXACT to perform a case sensitive exact match. In this article, we provide a simple example to calculate bonus for employees whose names are case-sensitive. If you meet similar scenarios ...

Basic Grade Calculation by VLOOKUP Function – Approximate Match

In Excel, except combination INDEX+MATCH, we can also apply other functions to search data, for example VLOOKUP function. Like MATCH function, VLOOKUP function is one of Excel lookup & reference functions that can perform approximate match or exact match by ...

Sidebar