VLOOKUP from different workbook

2
votes

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?

1
worksheet-functionvlookupexcel
well for whatever reason, now the update is working correctly...weird! – Leslie
I spoke too soon...if I edit the existing OdysseyDeposit sheet it works without opening, but if I create a new OdysseyDeposit workbook I still just get zeros all the way down if OdysseyDeposit is closed :( – Leslie
It is a bit off-topic but using =IFERROR(formula,valueIfError) will shrink your formula and give you same result. – zipa

1 Answers

0
votes

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)