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

Build Hyperlink With VLOOKUP in Excel

You might have come across a task in which you were assigned to build hyperlinks, which seems very easy, and if you are new to excel or don't have enough experience with it, then you might wonder about doing this ...

Calculate Total Cost with Excel VLOOKUP Function

In today's article we will show you how to calculate the total cost for a given weight using the Excel VLOOKUP function. This function will help us to find the appropriate unit price for that weight and then we can ...

Basic Price Discount Calculation with Excel VLOOKUP Function

We often encounter product discounts in our shopping. Depending on the level of spending, the mall will offer different percentages of discounts. Usually, the more you spend, the bigger the discount, while the less you spend, the smaller the discount. ...

Calculate Grades With VLookup in Excel

Why Should You Calculate Grades With VLookup Excel? If you're looking for a simple way to find out a student's grades, VLOOKUP Excel can do the trick. This function uses a lookup table to find the values and sort them ...

Convert State Names To Abbreviations

Assume that you got a task to convert the full state's name into the abbreviations in MS Excel, and for doing this task, you might choose to do it manually, which is an acceptable way only if you don't have ...

Sidebar