为什么一些excel文件必须为Vlookup开放,而不是其他? [英] Why do some excel documents have to be open for a Vlookup, and not others?
问题描述
这是一个嵌套的If / Vlookup的例子,除非Vlookup表格被称为销售页面 :$ / $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ [$ US $,销售页面Terrains.xlsx] IODC州'!$ A:$ B,2,FALSE),VLOOKUP($ X3,'[Salespage Territories.xlsx] IODC Zip'!$ A:$ D,4,TRUE) p>
VLOOKUP
在关闭的工作簿上工作正常,除非您的查找值超过255个字符或返回值超过同一限制
您引用的公式的问题不在于 VLOOKUP
但是 COUNTIF
- COUNTIF
在关闭的工作簿上不起作用 - 请参阅 here 。我不太确定你为什么在列C中检查X3,但可以使用MATCH,即
= 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将与封闭的工作簿一起使用
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
这篇关于为什么一些excel文件必须为Vlookup开放,而不是其他?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!