VLOOKUP with INDIRECT: The name of the Workbook i want my VLOOKUP to work on is given in a cell.

[leohimself]

Hello everyone,

I'm trying to use VLOOKUP to search on a table in a different Workbook (which is also open, say Workbook 2.xlsx on it's Sheet 1) than the current (say Workbook 1.xlsx). I want to write the name "Workbook 2.xlsx" in a cell of Workbook 1.xlsx Sheet 2 and then just use INDIRECT to take the name from there and use it with my VLOOKUP.

This is how my VLOOKUP looks: VLOOKUP(F12;INDIRECT('Sheet 2'!F2&"Sheet1'!$B$4:$E$39;4");False).
The Result I get is #REF!.
Could you guys help me and tell me what I'm doing wrong?

Just for background to make an easier understanding of what I'm trying to do:

The cell F12 of Workbook 1.xlsx Sheet 1 contains a name which should be looked up in Workbook 2.xlsx Sheet 1 and if there is an "ok" written in the 4th column of the table i have in Workbook 2.xlsx Sheet 1 then it should write the name in the cell where I'm using the VLOOKUP function. My function is longer with IFs and ORs but I just need help on this INDIRECT which is giving me headaches .

'Sheet 2'!F2 is the cell which contains the name of my Workbook 2.xlsx:precisly it contains " '[Workbook 2.xlsx] " which should be attached to Sheet1'!$B$4:$E$39.
When I analyse the #REF! error the result is:
VLOOKUP("Name";INDIRECT( "[Workbook 2.xlsx]Sheet1'!$B$4:$E$39;4");FALSE) but instead of getting the result from column 4 it just returns #REF!.

Thank you for your help!

 

[Chris Mack]

Does this work:

=VLOOKUP(F12;INDIRECT('Sheet 2'!F2&"Sheet1'!$B$4:$E$39");4;False)

Hope this helps,

Chris.

 

[Jonmo1]

Welcome to the board..

Can you post the working vlookup formula without Indirect?


The indirect would need to contain the entire string representing the book/sheet/range
In the following syntax:
'[BookName]SheetName'!A1:B100

the apostraphe's (') and brackets[] are required in the string within indirect.

 

[leohimself]

Ok, sorry for the inconvenience, I think I figured out my error:

Instead of using INDIRECT like mentioned before, now I used it like this:

VLOOKUP(F12;INDIRECT("'["&'Sheet2'!F2&"]Sheet1'!$B$4:$E$39");4;False) with the cell 'Sheet2'!F2 containing the name like this "Workbook 2.xlsx".
I have also closed brackets in the wrong position before.
Now it seems to work.

EDIT:

Thank you both for replying so fast. You were both correct!

@Jonmo1: The VLOOKUP without INDIRECT would've looked like this:

VLOOKUP(F12;'[Workbook 2.xls]Sheet 1'!$B$4:$E$39;4;FALSE)

 

[Jonmo1]

You're welcome.


FYI, the apostraphes are technically only required if there are spaces in the bookname and/or sheetname.
But it's just easier to always put them there, so you don't have to think about whether they're needed or not.

 

[Chris Mack]

You're welcome!