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:   =VLO...
excel
Formulas and Functions
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