问题描述
我有一个包含[Year&行中的星期数"和行1中的[员工姓名]给出了可用的每周小时数.
I have an Excel sheet containing a matrix of [Year & Week number] in rows and [name of Employees] in row1 giving values of available weekly hours.
我能够成功使用Excel公式
I am able to successfully use the Excel formula
=LOOKUP(2,1/((A:A=2018)*(B:B=31)),D:D)
在单元格F2处
进行反向查找.正确给出结果 15 .
但是,我想通过在知道该雇员时识别该列,将上述公式中的范围D:D替换为动态范围.
However, I wanted to replace range D:D in the above formula to a dynamic range by identifying the column when the employee is known.
所以我尝试用公式代替
SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1","") &":"&SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1","")
公式的这一部分有效,并为Employee2提供 D:D .这显示在F4单元格中起作用.
This portion of the formula works and gives D:D for Employee2. This is shown to work in cell F4.
但是修改后的公式在单元格F6上给出了 #Value!错误.
But the revised formula gives an error of #Value! at cell F6.
它说:公式中使用的值是错误的数据类型."修改后的公式无效:
It says "A value used in the formula is of the wrong data type."The revised formula which does not work is:
=LOOKUP(2,1/((A:A=2018)*(B:B=31)),SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1","") &":"&SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1",""))
我希望有人可以帮助我告诉我在尝试用SUBSTITUTE,ADDRESS& amp;组合替换LOOKUP公式中的范围D:D时出错的地方.匹配功能.
I hope someone can help show me where I am making an error in trying to replace range D:D in the LOOKUP formula with the combination of SUBSTITUTE, ADDRESS & MATCH functions.
感谢所有尝试提前提供帮助的人.
Thanks to all trying to help in advance.
推荐答案
您不能只插入字符串,这是SUBSTITUTE返回到公式中的内容.
You cannot just plug a string, which is what SUBSTITUTE returns into a formula.
您可以使用INDERICT将字符串转换为可行的引用:
You can use INDERICT to turn the string into a viable reference:
INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1","") &":"&SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1",""))
但是INDIRECT和ADDRESS都是易变的.
But both INDIRECT and ADDRESS are volatile.
使用INDEX来返回正确的列:
Use instead INDEX to return the correct column:
INDEX(A:AAA,0,MATCH("Employee2",1:1,0))
所以您的公式是:
=LOOKUP(2,1/((A:A=2018)*(B:B=31)),INDEX(A:AAA,0,MATCH("Employee2",1:1,0)))
这篇关于无法在Excel中的LOOKUP函数中将SUBSTITUTE(ADDRESS(MATCH)))组合用作范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!