英文:
Why does my INDEX-MATCH formula in Excel return 0 as the result?
问题
我在Excel文件中有以下内容：
- 在第一个工作表中，名为“主表”，有一个包含2列的表格。
- 列A称为“姓名”并包含姓名列表。
- 列B称为“积分”。
在第二个工作表中，名为“积分”，也有一个表格。
列A称为“姓名”并包含与第一个工作表相同的姓名列表。
随后的列以特定日期命名，并包含积分。
我想在第一个工作表中的“积分”列中显示在第二个工作表中为特定姓名添加的最新积分。这样，在“主表”工作表中，我可以始终拥有最新的可用数据。
屏幕截图：
我尝试了以下公式：
=INDEX(Points!$B:$Z, MATCH(A2, Points!$A:$A, 0), MAX(IF(Points!$A$1:$Z$1<>""， COLUMN(Points!$A$1:$Z$1)-COLUMN(Points!$A$1)+1)))
由于我希望在第二个工作表中继续添加包含数据的列，因此我认为我应该扩展范围以包括未来的列，例如“Z”。
但结果总是为0。
有人能帮忙吗？
谢谢！
Arky
英文:
I have the following in an Excel file:
- In the first sheet, named "Master" there is a table with 2 columns.
- Column A is called "Names" and have a list of names
- Column B is called "Points"
In the second sheet, called "Points" there is also a table.
Column A is called "Names" and have the same list of names as the first sheet
Subsequent columns are named after specific dates and contains points.
I want to display in the first sheet, column "Points" the latest points added in the second sheet for a certain name. In this way, in the "Master" sheet, I can always have the latest available data.
Screenshots:
I tried the following formula:
=INDEX(Points!$B:$Z, MATCH(A2, Points!$A:$A, 0), MAX(IF(Points!$A$1:$Z$1<>"", COLUMN(Points!$A$1:$Z$1)-COLUMN(Points!$A$1)+1)))
Since I want to keep adding columns with data in the second sheet, I figured that I would extend the range until the column "Z" for future proofing.
The results are always 0
Any takers?
Thanks!
Arky
答案1
得分: 1
你的索引范围从列B开始，如果你将其更改为从列A开始，它会起作用。
但我建议如果你使用表格引用的话。
在这种情况下，由于您的主表和积分表在同一行中具有名称，您可以通过以下方式实现：
=INDEX(Table2[#All],ROW([@Names]),COLUMNS(Table2))
（其中Table2是工作表Points中的表格名称）：
否则，您需要对其进行索引并使用匹配：
=INDEX(INDEX(Table2,,COLUMNS(Table2)),MATCH([@Names],Table2[Names],0))
在Office 365中：
=@TAKE(Table2,,-1) 如果顺序相同，
或者 =INDEX(TAKE(Table2,,-1),XMATCH([@Names],Table2[Names])) 如果顺序不同。
或者，您可能想要完全跳过使用表格来处理您的结果，而是使用以下方法（在Office 365中）：
=CHOOSECOLS(Table2,1,COLUMNS(Table2))
英文:
Your indexed range started at column B, where it works if you change it to start at A.
However I recommend using Table references if you use them.
In this case, since your Master and Points tables have the names in the same row, you could accomplish this by:
=INDEX(Table2[#All],ROW([@Names]),COLUMNS(Table2))
(where Table2 is the name of the table in sheet `Points'):
Otherwise you need to INDEX that and use match:
=INDEX(INDEX(Table2,,COLUMNS(Table2)),MATCH([@Names],Table2[Names],0))
In Office 365:
=@TAKE(Table2,,-1) in case of same order,
or =INDEX(TAKE(Table2,,-1),XMATCH([@Names],Table2[Names])) if mixed.
Alternately you may want to skip using a table for your results at all and use the following (in Office 365):
=CHOOSECOLS(Table2,1,COLUMNS(Table2))
答案2
得分: 0
使用 MATCH() 函数。通过匹配 MAX(dates)，您可以检测到积分的最新列。尝试-
=INDEX($F$2:$I$5,MATCH(A2,$E$2:$E$5,0),MATCH(MAX($F$1:$I$1),$F$1:$I$1,0))
如果要引用 Sheet2，使用-
=INDEX(Sheet2!$B$2:$E$5,MATCH(A2,Sheet2!$A$2:$A$5,0),MATCH(MAX(Sheet2!$B$1:$E$1),Sheet2!$B$1:$E$1,0))
英文:
Use MATCH() function. By matching MAX(dates) you can detect latest column for points. Try-
=INDEX($F$2:$I$5,MATCH(A2,$E$2:$E$5,0),MATCH(MAX($F$1:$I$1),$F$1:$I$1,0))
Incase to refer Sheet2, use-
=INDEX(Sheet2!$B$2:$E$5,MATCH(A2,Sheet2!$A$2:$A$5,0),MATCH(MAX(Sheet2!$B$1:$E$1),Sheet2!$B$1:$E$1,0))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论