Lookup value from different data sources

    The mentioned formula allows the user to extract the output for Lookup value from different source

  • Data exists in the same tab
  • Data exists in different tabs

    •  

    Functions,Properties and methods Used:

  • Vlookup Function
  • IfError Function
  • Formula Property of Range Object
  • FillDown Method of Range Object

    •  

    Click on Below mentioned Image to watch the video:

    Download The Workbook

    Code:

    Sub VLookup_When_TableArray_From_Multiple_Sources()

    ActiveSheet.Range("B2:B13").ClearContents
    Application.Wait (Now + TimeValue("00:00:01"))

    Dim Fsh As Worksheet
    Set Fsh = ActiveWorkbook.Sheets("Fruits")
    Dim FRng As String
    FRng = Fsh.Name & "!" & Fsh.Range("A2:B6").Address(True, True)

    Dim Ssh As Worksheet
    Set Ssh = ActiveWorkbook.Sheets("Study")
    Dim Srng As String
    Srng = Ssh.Name & "!" & Ssh.Range("A2:B5").Address(True, True)

    Dim Sysh As Worksheet
    Set Sysh = ActiveWorkbook.Sheets("System")
    Dim SyRng As String
    SyRng = Sysh.Name & "!" & Sysh.Range("A2:B3").Address(True, True)

    ActiveSheet.Range("B2").Formula = _
    "=IFERROR(VLOOKUP(A2," & FRng & ",2,0),IFERROR(VLOOKUP(A2," & Srng & ",2,0), IFERROR(VLOOKUP(A2," & SyRng & ",2,0), ""Doesn't Exists"")))"

    'ActiveSheet.Range("B2").Formula = "=IFERROR(VLOOKUP(A2,$I$2:$J$6,2,0),
    IFERROR(VLOOKUP(A2,$M$2:$N$5,2,0), IFERROR(VLOOKUP(A2,$F$2:$G$3,2,0), ""Doesn't Exists"")))"


    'ActiveSheet.Range("B2").Formula = "=IFERROR(VLOOKUP(A2,Fruits!$A$2:$B$6,2,0),
    IFERROR(VLOOKUP(A2,Study!$A$2:$B$5,2,0),
    IFERROR(VLOOKUP(A2,System!$A$2:$B$3,2,0), ""Doesn't Exists"")))"
     ActiveSheet.Range("B2:B13").FillDown

    End Sub

     

     

     

     

Hi Everyone,
Welcome to VBA Macros Tutorial. This Tutorial provides comprehensive knowledge on VBA Macros to the user. Hope it enhances your coding skills. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli