无法在Excel中的LOOKUP函数中将SUBSTITUTE(ADDRESS(MATCH)))组合用作范围 [英] Unable to use SUBSTITUTE(ADDRESS(MATCH))) combination as a range in a LOOKUP function in Excel

问题描述

我有一个包含[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)))组合用作范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！