Why do some excel documents have to be open for a Vlookup, and not others?

Question

We work with Vlookup functions all of he time at my job. Specifically, we use Vlookup functions that reference tables on seperate sheets. There is a very odd occurance with these functions. It seems as if certain functions require the Vlookup table sheet to be open, and will return a #VALUE error if the sheet is not open. Other functions return a correct result with the Vlookup table sheet not open. Can someone give me an explanation by chance?

Here is an example of a nested If/Vlookup that will NOT run unless the Vlookup table sheet known as Sales Page Territories is open:

=IF(1>COUNTIF('[Salespage Territories.xlsx]IODC Zip'!$C:$C,$X3),VLOOKUP($X3,'[Salespage Territories.xlsx]IODC State'!$A:$B,2,FALSE),VLOOKUP($X3,'[Salespage Territories.xlsx]IODC Zip'!$A:$D,4,TRUE))

Solution

VLOOKUP works OK on closed workbooks unless you have lookup values with more than 255 characters or if return values go above that same limit

The problem with the formula you quote is not with VLOOKUP but COUNTIF - COUNTIF doesn't work on closed workbooks - see here. I'm not quite sure why you are checking for X3 in column C but you could use MATCH instead, i.e.

=IF(ISNA(MATCH($X3,'[Salespage Territories.xlsx]IODC Zip'!$C:$C,0)),VLOOKUP($X3,'[Salespage Territories.xlsx]IODC State'!$A:$B,2,FALSE),VLOOKUP($X3,'[Salespage Territories.xlsx]IODC Zip'!$A:$D,4,TRUE))

MATCH will work with closed workbooks

Comments

[Doug Glancy]

-1 You can certainly get correct results from external links without opening the workbook. What does "Open in the background" mean?

[MikeD]

I doubt .... create an external link and then move the referenced workbook to another file location, close / reopen your sheet and calculate ... you'll get #N/A's or won't retrieve new values (which would contradict your statement) ... "in the background" means that you don't see the workbook open (aequivalent to .Visible=False)

[Doug Glancy]

Saying that you can break external references, and you certainly can, is far different than saying they need to be open to work. Also the Workbook object has no Visible property. And if all referenced workbooks were open you'd see them in the Visual Basic Editor, which you don't.

[Charles Williams]

If you have "Save External Link Values" checked in Options then Excel caches retrieved external link values so that if you don't refresh then the calculation uses the cached values.