Vlookup in multiples worksheet in another workbbok

[SimonD90]

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

[#2]

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

[#3]

@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

[#4]

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