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