Vlookup in multiples worksheet in another workbbok

%3CLINGO-SUB%20id%3D%22lingo-sub-1706098%22%20slang%3D%22en-US%22%3EVlookup%20in%20multiples%20worksheet%20in%20another%20workbbok%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1706098%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20knows%20how%20to%20vlookup%20over%20multiple%20sheets%20in%20another%20workbook%20with%20either%20function%20or%20macro%3F%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20trying%20the%20following%20function%2C%20but%20it%20doesn't%20work%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(%24B%243(INDIRECT(%22'%5BBook2%5D%22%26amp%3B%24A%241%26amp%3B%22'!%24B%243%3A%24D%247%22)%2C2%2CFALSE))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20can%20help%2C%20it%20would%20save%20me!%20Thanks%20for%20reading.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimon%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1706098%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1707455%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20in%20multiples%20worksheet%20in%20another%20workbbok%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1707455%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F806615%22%20target%3D%22_blank%22%3E%40SimonD90%3C%2FA%3E%26nbsp%3B%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EDo%20the%20following%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EAnywhere%20in%20blanks%20cell%20write%20Sheet%20name%20along%20with%20Workbook's%20name.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5Bbook1.xlsm%5DSheet1%3C%2FP%3E%3CP%3E%5Bbook1.xlsm%5DSheet2%3C%2FP%3E%3CP%3E%5Bbook1.xlsm%5DSheet3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EIf%20Workbook%20is%20in%20different%20path%20then%20you%20need%20to%20add%20the%20Full%20File%20Path.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EC%3A%5CUsers%5CUser%5CDesktop%5Cyour%20folder%20name%20here%5C%5Bbook1.xlsm%5DSheet1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3ESelect%20those%20cells%20and%20assign%20NAME%20to%20the%20range%2C%20you%20may%20use%20%3CSTRONG%3ECtrl%2BF3.%3C%2FSTRONG%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EUse%20formula%20like%20this%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DVLOOKUP(%24B%243(INDIRECT(%22'%22%26amp%3BShtList%26amp%3B%22'!%24B%243%3A%24D%247%22))%2C2%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSPAN%3EWhere%20%3CSTRONG%3EShtList%3C%2FSTRONG%3E%20is%20the%20name%20given%20to%20Range%2C%20you%20have%20entered%20Sheet's%20name.%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CEM%3E%3CSPAN%3EOr%20you%20may%20use%20this%20syntax%20also%3A%3C%2FSPAN%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIFERROR(VLOOKUP(%24B%243%2C%5BBook1.xlsm%5DSheet1!%24B%243%3A%24D%247%2C%202%2C%20FALSE)%2C%20IFERROR(VLOOKUP(%24B%243%2C%5BBook1.xlsm%5DSheet2!%24B%243%3A%24D%247%2C%202%2C%20FALSE)%2C%20%22Not%20found%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3CSPAN%3E%3CSPAN%3EAdjust%20cell%20references%2C%20File%20Path%20%26amp%3B%20Range%20Name%20as%20needed.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1714891%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20in%20multiples%20worksheet%20in%20another%20workbbok%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1714891%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much%20for%20helping!%20And%20sorry%20for%20the%20late%20response%2C%20my%20account%20wouldn't%20connect.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20actually%20need%20to%20lookup%20for%20a%20value%20in%20D1%20in%20another%20workbook%20and%20multiple%20sheets%20(the%20name%20of%20the%20sheet%20being%20in%20a%20dropdown%20menu%20in%20B3).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20need%20both%2C%20the%20value%20to%20look%20for%20and%20the%20name%20of%20the%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20try%20your%20formula%20I%20get%20a%20%23REF%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20wrote%20the%20following%20formula%20and%20it%20works%2C%20but%20only%20when%20the%20other%20workbook%20is%20open.%20Do%20you%20know%20how%20to%20make%20this%20work%20when%20the%20workbook%20is%20closed%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D(VLOOKUP(%24D1%2CINDIRECT(%22'%5BBook1.xlsm%5D%22%26amp%3BB%243%26amp%3B%22'!%24C%247%3A%24D%2412%22)%2C2%2CFALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3CP%3ESimon%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

3 Replies

@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.

Highlighted

@Rajesh-S 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

 

 

Highlighted

@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.