December 11, 2017

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:


2 thoughts 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.

Comments are closed.