Excel索引匹配多行结果 [英] Excel index match multiple row results

查看:198
本文介绍了Excel索引匹配多行结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我遇到了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...

我有一个大型数据集,其中包含YearNameTotal 1Total 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))

我遇到的问题是将多年数据添加到我的第一数据集中的结果.有没有一种方法可以基于nameyear匹配行,然后返回相应列的结果?

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))

=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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆