Author Archives: Dinesh Kumar Takyar

About 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

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