如何根据Excel中输入单元格中的文本创建具有动态返回数组的Xlookup？
我想在一个工作簿中创建一个摘要表，该工作簿使用Xlookup在另一个工作簿中的数据表中匹配标准。我希望Xlookup的返回阵列根据摘要工作簿中表的标题进行更改。
在指定命名表和列时，我可以成功提取所需的数据。
= XlookUp（B9＆amp; b10，'2022 Farnam Performance Data Collector.xlsx'！ManagerKpi [[＃all]，[Entity Name]]＆amp;'2022 Farnam performance data Collector.xlsx' ”
，[公制名称]，'2022 Farnam Performance Data Collector.xlsx'！ManagerKpi [[＃all]，[1/21/2022]]，“找不到 能够根据摘要表标头更改[1/21/2022]]参考。
我尝试了间接参考并获得#ref。检查计算步骤#REF从我指定的单元格中提取文本时会发生#REF错误。另外，如果可能的话，我宁愿在公式中没有间接的话。
= XlookUp（B9＆amp; b10，'2022 Farnam Performance Data Collector.xlsx'！ManagerKpi [[＃all]，[Entity Name]]＆amp;'2022 Farnam performance data Collector.xlsx' ，[metric名称]]，间接（“'2022 Farnam Performance Data Collector.xlsx'！ManagerKpi [[＃all]，[＆amp; 1/1/21/21/2022＆amp;“”] ，）
I want to create a summary table in one workbook that uses Xlookup to match criteria in a table of data in another workbook. I would like the return array of the Xlookup to change based on the header of the table in the summary workbook.
I can successfully pull the data I need when specifying the named table and column like this.
=XLOOKUP(B9&B10,'2022 Farnam Performance Data Collector.xlsx'!ManagerKPI[[#All],[Entity Name]]&'2022 Farnam Performance Data Collector.xlsx'!ManagerKPI[[#All],[Metric Name]],'2022 Farnam Performance Data Collector.xlsx'!ManagerKPI[[#All],[1/21/2022]],"Not Found",,)
However, I want to be able to change the [1/21/2022]] reference based on the summary table header.
I tried an indirect reference and got #Ref. Checking calculation steps the #REF error occurs when pulling text from the cell I specified. Also if at all possible I would prefer to not have an indirect in my formula if at all possible.
=XLOOKUP(B9&B10,'2022 Farnam Performance Data Collector.xlsx'!ManagerKPI[[#All],[Entity Name]]&'2022 Farnam Performance Data Collector.xlsx'!ManagerKPI[[#All],[Metric Name]],INDIRECT("'2022 Farnam Performance Data Collector.xlsx'!ManagerKPI[[#All],["&1/21/2022&"]]", TRUE),"Not Found",,)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
屏幕截图参考 -
a）删除表公式（file-＆gt; options - ＆gt; formulas - ＆gt;使用表名称[deselect/uncheck box]
b）包括列查找的索引参考 - 请参阅索引以下示例搜索源.xlsx文件中的2022/1/21（格式）
（底部）将row1检索到dest.xlsx文件（顶部）：
voila！
screenshots refer -
a) remove table formulae (file -> options -> formulas -> Use table names [deselect/uncheck box]
b) Include an index reference for the column lookup - SEE index component of following example that searches for 2022/1/21 (date formatted) in the SOURCE.xlsx file
(bottom) to retrieve row1 to DEST.xlsx file (top):
voila!