Excel 索引匹配多行结果 [英] Excel index match multiple row results
问题描述
我遇到了一个 Excel 问题,希望有人能提供帮助.我通读了 10 到 15 个相似的主题,但我什么也做不了.这就是我所在的地方...
I'm stuck on an Excel problem and am hoping someone can assist. I read through 10-15 topics that are similar, but I wasn't able to get anything to work. Here is where I'm at...
我有一个大数据集,其中包含 Year
、Name
、Total 1
、Total 2
(和 20 多个其他列).基于年度总数,相同的名称出现在多行中.在单独的工作表上,我有另一个包含 Name 的数据集,并希望将第一张表格中的数据提取到如下所示的列中.
I have a large data set containing columns for Year
, Name
, Total 1
, Total 2
(and 20+ other columns). The same names appear in multiple rows based on the yearly totals. On a separate sheet, I have another data set containing Name and would like to pull the data from sheet one into columns as shown below.
我过去仅使用一年作为初始数据集,并使用以下公式完成此操作:
I have done this in the past using only one year as the initial data set with the following formula:
=INDEX(DATARANGE,MATCH([@Name],DATARANGE[Name],0),MATCH("Total 1",DATARANGE[#Headers],0))
我遇到的问题是将多年的数据添加到我的第一个数据集的结果.有没有办法根据 name
和 year
匹配行,然后返回相应列的结果?
The problem I am having is the result of adding multiple years of data to my 1st data set. Is there a way to match the row based on name
and year
and then return the results of the appropriate column?
推荐答案
把第一个 MATCH 函数改成这样:
Change the first MATCH function to something like this:
=MATCH(1,INDEX(([@Name]=DATARANGE[Name])*([@Year]=DATARANGE[Year]),0),0)
所以作为你整个公式的一部分
so as part of your whole formula that would be this
=INDEX(DATARANGE,MATCH(1,INDEX(([@Name]=DATARANGE[Name])*([@Year]=DATARANGE[Year]),0),0),MATCH("Total 1",DATARANGE[#Headers],0))
另一种仅用于返回数字的方法(如此处)是这样的:(为简单起见,使用单元格引用).
Another way you can use for returning numbers only (as here) is like this: (with cell refs for simplicity).
=SUMPRODUCT((A2:A9=2013)*(B2:B9="name x")*(C1:D1="Total 1"),C2:D9)
这篇关于Excel 索引匹配多行结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!