Forum Discussion

SimonD90's avatar
SimonD90
Copper Contributor
Sep 24, 2020

Vlookup in multiples worksheet in another workbbok

Hello everyone,

 

Does anyone knows how to vlookup over multiple sheets in another workbook with either function or macro? 

I have been trying the following function, but it doesn't work:

 

=VLOOKUP($B$3(INDIRECT("'[Book2]"&$A$1&"'!$B$3:$D$7"),2,FALSE))

 

If anyone can help, it would save me! Thanks for reading.

 

Simon

excel
Formulas and Functions

3 Replies

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Sep 24, 2020

    SimonD90 ,,

     

    Do the following:

     

    • Anywhere in blank cells write Sheet name along with Workbook's name.

     

    [book1.xlsm]Sheet1

    [book1.xlsm]Sheet2

    [book1.xlsm]Sheet3

     

    If Workbook is in different path then you need to add the Full File Path.

     

    C:\Users\User\Desktop\Your Folder Name here\[book1.xlsm]Sheet1

     

    • Select those cells and assign NAME to the range, you may use Ctrl+F3.

     

    Use formula like this:

     

     

    =VLOOKUP($B$3(INDIRECT("'"&ShtList&"'!$B$3:$D$7")),2,FALSE)

     

     

    • Where ShtList is the name given to Range, you have entered Sheet's name.

     

    Or you may use this syntax also:

     

     

    =IFERROR(VLOOKUP($B$3,[Book1.xlsm]Sheet1!$B$3:$D$7, 2, FALSE), IFERROR(VLOOKUP($B$3,[Book1.xlsm]Sheet2!$B$3:$D$7, 2, FALSE), "Not found"))

     

     

     

    Adjust cell references, File Path & Range Name as needed.

    • SimonD90's avatar
      SimonD90
      Copper Contributor
      Sep 25, 2020

      Rajesh_Sinha Thank you so much for helping! And sorry for the late response, my account wouldn't connect.

       

      I actually need to lookup for a value in D1 in another workbook and multiple sheets (the name of the sheet being in a dropdown menu in B3).

       

      So I need both, the value to look for and the name of the sheet.

       

      When I try your formula I get a #REF error.

       

      I have wrote the following formula and it works, but only when the other workbook is open. Do you know how to make this work when the workbook is closed?

       

      =(VLOOKUP($D1,INDIRECT("'[Book1.xlsm]"&B$3&"'!$C$7:$D$12"),2,FALSE)

       

      Thanks 

      Simon

       

       

      • Rajesh_Sinha's avatar
        Rajesh_Sinha
        Iron Contributor
        Sep 26, 2020

        SimonD90 ,,

         

        Since you are working with closed workbook then, need to add the File path, it may be like this.

         

         

         

        =VLOOKUP(D1,'C:\My Documents\Temp\[Demo.xlsx]Sheet'!C1:D20,2,FALSE)

         

         

         

        My suggestion is,, use the method I've shown is better & faster also. and no need to use/write workbooks separately.

         

        Replace cell value  with this:

         

         

        'C:\Users\User\Desktop\Folder Name\[book1.xlsm]Sheet1'

         

         

         

        Note: You need to adjust Drive, Folder, Workbook, Sheet & Range as needed.

Resources