VLOOKUP from different workbook

Question

I have 2 workbooks....OdysseyDeposit where my source data is and Civil Bank Accnt Recon which contains VLOOKUP to the OdysseyDeposit.

Here is the VLOOKUP function that I am using:

=IF(ISNA(VLOOKUP(A2,[OdysseyDeposit.xls]Sheet1!$A$9:$L$19, 12, 0)), 0, 
VLOOKUP(A2,[OdysseyDeposit.xls]Sheet1!$A$9:$L$19, 12, 0))

The ISNA function is there so if the value is not found in OdysseyDeposit I get a 0 in my Civil Bank Accnt Recon workbook so my addition works correctly.

However, I was under the impression that OdysseyDeposit did NOT have to be open in order to retrieve the values, but if it is closed I don't get any values, just zeros.

Am I incorrect about VLOOKUP working when the source file is closed? Could it not be updating when it is closed because of the ISNA function?

Answer 1

You are correct the workbook does not need to be open to retrieve values but a link between them does need to be established.

1. You should open both workbooks.
2. In the formula bar type your formula and select the ranges from the other workbook.
3. If you close both workbooks and open the one containing the formula you will be asked to establish a link/reference between the two (Excel 2007)