英文:
Xlookup issue with Indirect function in Google Sheets
问题
我正在使用带有间接引用的xlookup函数来确定要搜索的行范围的特定日期范围。有3000多行数据和重复的数值，这帮助我排除了重复的数值，以免出现来自先前输入的错误值。当我手动输入要搜索的行范围时，xlookup最初运行得很好，但是当引入间接引用时，我收到了我编程的错误消息（这让我认为我的函数没有错误）。
当我使用直接引用（也就是我必须直接在公式中更改日期范围）时，它运行得很好！
=xlookup(T3,Statistics!F203:F215,Statistics!A203:215,"Not Scheduled",1,1)
然而，当我使用间接引用来引用特定范围（这样我可以在一个单元格上调整日期范围并重新计算引用的行）时，我收到了我编程的错误消息。
=xlookup(T3,indirect(Statistics!Q11&":"&Statistics!R11),indirect(Statistics!T11&":"&Statistics!U11),"Not Scheduled",1,1)
Q11=F203
R11=F215
T11=A203
U11=A215
正确的答案位于F205行，它位于函数中指定的范围内。
通常情况下，我在使用我的函数时会找到解决方法或者发现拼写错误，但我要诚实地说这个问题让我困惑。另一页上的引用是相同的。只是作为说明，我还尝试了使用Concatenate函数将两个单元格合并然后调用间接引用，但结果相同。如果能提供帮助，将不胜感激，因为这将为交通系统的验证添加验证，而不是在两个系统之间进行双重输入。
英文:
I am utilizing a xlookup with indirect references function with certain date ranges to determine the range of rows to search in. With 3k rows of data and repeating values this helped me pair down to non-repeating values so I don't have an errant value from an earlier entry. The xlookup initially worked great when I manually inputted range of rows to search in but when indirect was introduced I am getting my error message I programmed (leading me to believe there isn't an error with my function)
When I utilize the direct references (meaning I'd have to change the date ranges directly in formula) it works great!
`=xlookup(T3,Statistics!F203:F215,Statistics!A203:215,"Not Scheduled",1,1)`
However when I use indirect to reference the specific range (so that I can adjust the date range over one cell and the reference rows recalculate) I get the error message I programmed.
`=xlookup(T3,indirect(Statistics!Q11&":"&Statistics!R11),indirect(Statistics!T11&":"&Statistics!U11),"Not Scheduled",1,1)`
Q11=F203
R11=F215
T11=A203
U11=A215
The correct answer lies in Row F205 which is within the ranges specified in the function.
Usually I figure these out or find a typo when utilizing my functions but I'll be honest and say this has me stumped. The references on the other page are identical. Just for note I also tried a Concateate function to combine the two cells before calling indirect with the same result. Any help would be greatly appreciated as this would add verification to a transportation system instead of double entry across two systems.
答案1
得分: 0
根据我的看法，我认为你遗漏了工作表的名称。你引用了单元格，但没有明确INDIRECT函数要查找"Statistics"工作表。尝试添加它：
=xlookup(T3,indirect("Statistics!"&Statistics!Q11&":"&Statistics!R11),indirect("Statistics!"&Statistics!T11&":"&Statistics!U11),"Not Scheduled",1,1)
关于在"Not Scheduled"之后的那个1，你确定不只想要精确匹配吗？是否考虑使用0代替1。
英文:
As far as I see it, what I think you're missing is the name of the sheet. You're referencing to the cells but not clarifying that the INDIRECT function has to look into "Statistics". Try adding it:
=xlookup(T3,indirect("Statistics!"&Statistics!Q11&":"&Statistics!R11),indirect("Statistics!"&Statistics!T11&":"&Statistics!U11),"Not Scheduled",1,1)
About that 1 after "Not Scheduled", are you sure you don't want an exact match only? Meaning to use a 0 instead of 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论