带有嵌套的if if vlookup和变量范围
我一直在努力使Vlookup在没有VBA的情况下更有效地工作，而我认为是一个动态的（名称）范围，而没有嵌套功能。 我是新来的，试图与其他帖子一起找到解决方案，但无法解决。
我有一个带有项目代码的工作簿和一个vlookup，它引用了另一个封闭的工作簿（因此函数间接不起作用）具有成本值。在单元格D3中，有一个数据验证单元格，用户可以在其中选择其他工作簿中的哪个表（例如file001或file 002），更改vlookup并返回正确的成本。
不幸的是，我无法更有效地工作。 我尝试了间接，但是只有在另一个工作簿打开并且需要关闭时才有效。
我想要一些简单的东西，例如：
= vlookup（b3，间接（“'[book3.xlsx]”＆amp; d3＆amp;“'！a3：b6”），2，false）
< < em>不起作用的
使用的内容，每次都在检查file001，002或003，..等
=IFNA(IFS($K$2="","?",$K$2="File001",PROPER(VLOOKUP(UPPER($E13),NameRange001,4,FALSE)),$K$2="File0002",PROPER(VLOOKUP(UPPER($E13),NameRange002,4,FALSE)),$K$2="File003",PROPER(VLOOKUP(UPPER($E13),NameRange003,4,FALSE)),$K$2="File004",PROPER(VLOOKUP(UPPER($E13),NameRange004,4,FALSE)),$K$2="File005",PROPER(VLOOKUP(UPPER($E13),NameRange005,4,FALSE)),$K$2="File006",PROPER(VLOOKUP(UPPER($E13),NameRange006,4,FALSE))),"")
。
...而不是我现在 href =“ https://i.sstatic.net/boxrz.png” rel =“ nofollow noreferrer”>
我需要添加其他WB中的更多床单，例如，到file0030，使嵌套的IF非常长。有人请问如何使这项工作更有效吗？我一直在尝试改善Maaany的日子。
提前致谢！！
I've been trying to get a Vlookup to work more efficiently without VBA with I think is a dynamic (name) range, and without nested IF functions.
I'm new here and have tried to find the solution with other posts, but couldn't work it out.
I have one workbook with item codes and a vlookup, which references to another closed workbook (so function Indirect doesn't work) with cost values. In cell D3 there is a data validation cell where the user can select which sheet in the other workbook should be referenced (e.g. File001 or File002), changing the Vlookup and returning the correct cost.
Unfortunately I can't get it to work more efficiently.
I've tried Indirect, but that only works when the other workbook is open and I need it to be closed.
I'd like something simple such as:
=VLOOKUP(B3,INDIRECT("'[Book3.xlsx]" & D3 & "'!A3:B6"),2,FALSE)
which doesn't work
...and not what I use now, where it checks every time if it is File001, or 002, or 003,.. etc. and then vlookup:
=IFNA(IFS($K$2="","?",$K$2="File001",PROPER(VLOOKUP(UPPER($E13),NameRange001,4,FALSE)),$K$2="File0002",PROPER(VLOOKUP(UPPER($E13),NameRange002,4,FALSE)),$K$2="File003",PROPER(VLOOKUP(UPPER($E13),NameRange003,4,FALSE)),$K$2="File004",PROPER(VLOOKUP(UPPER($E13),NameRange004,4,FALSE)),$K$2="File005",PROPER(VLOOKUP(UPPER($E13),NameRange005,4,FALSE)),$K$2="File006",PROPER(VLOOKUP(UPPER($E13),NameRange006,4,FALSE))),"")
Code example:
I need to add more sheets from the other wb, so e.g. up to File0030, making the nested IFs extremely long. Does anyone please have an idea how to make this work more efficiently? I've been trying to improve this for maaany days.
Thanks in advance!!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论