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))
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