Forum Discussion

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

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

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.