Using Vlookup with Indirect Function in VBA

How to use Vlookup with the Indirect function in VBA to get data from multiple worksheets. Earlier we had learnt how to use the indirect function in VBA. We also learnt different ways to use Vlookup to analyze data. Watch the video below to learn how to use vlookup with indirect function in vba:

 

Watch this video on YouTube.

Here’s the complete VBA code:

Sub vlookupWithIndirect()

‘Using the Vlookup function with the Indirect function to get multiple values

Range(“b2”) = “=vlookup($A2,indirect(B$1 & “”!$A$1:$B$4″”),2,FALSE)”

‘Now Autofill horizontally

Range(“B2”).AutoFill Destination:=Range(“B2:D2”), Type:=xlFillDefault

‘now autofill vertically

Range(“B2:D2”).AutoFill Destination:=Range(“B2:D4”), Type:=xlFillDefault

End Sub

Here’s another variant shared by one of our YouTube visitors:

Sub vlookupIndirectFunctions()
Range(“B2”) = “=VLOOKUP($A2,INDIRECT(B$1&””!$A$1:$B$4″”),2,0)”
End Sub

Further Reading:

INDIRECT Function

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Using Vlookup with Indirect Function in VBA”

  1. Hi sir, thank for making these vba code.

    We had a query as below.

    We have data

    Column a- name of staff
    Column B- id number
    Column C- JOINING DATE
    column d- exit date
    Column e- line manager
    Column f- line manager brid

    Another sheet
    Column a- Line manager brid
    Column b- line manager email id

    I need this code related to capture data with filter line manager name accross all their under working staff including all column & take email id this line manager from another sheet & automatically sent mail with subjet, body this filtered data.

Leave a Reply

Your email address will not be published. Required fields are marked *