Excel 2013 VLOOKUP()基于多个条件，检索了多行 [英] Excel 2013 VLOOKUP() based on multiple criteria, retrieving multiple rows

问题描述

我有一个(可以相信)可以通过excel解决的问题，并且可能不需要VBA(我可能错了).我相信可以通过嵌套函数来解决，但是我尝试过的公式没有用.

I have an issue that can (I believe) be solved by just excel, and may won't require VBA (I could be wrong). I believe it can be solved by nested functions but the formula I've tried has not worked.

这是我的数据:

 Name               Report #          Name
Mark Doe            ReportXXX       Mark Doe
Connie Doe          ReportYYY       Connie Doe
Debbie Doe          REPORTYYY       Debbie Doe  
Valerie Doe         FSMVALTR1       Valerie Doe
Jeff Doe            FSMVALTR1       Jeff Doe
Andy Doe            RAZXYBCA1       Andy Doe    
Ryan Doe            RAZXYBCA1       Ryan Doe    
Andy Doe            RAZ111111       Jill Doe    
Ryan Doe            RAZ222112       Amanda Doe

此列表在NAME和REPORT#的第一列中持续约4000行.在第二个名称"列中，我有约160行.

This list goes on for about ~4000 rows in the first NAME and REPORT # columns. In the second NAME column I have ~160 rows.

第二名"列标识了实际使用该报告的所有用户，没有重复项.名称和报告#这两个列具有许多重复项，因为用户可以访问多个报告，并且其中许多是用于不同目的的同一报告.由于第二个名称"列的行数很少，因此名称并不能完全匹配，这可以在两个名称"列的底部附近看到.

The second name column identifies all the users who actually use the report, with no duplicates. The two name and report # columns have many duplicates, since users have access to multiple reports, and many of them are the same report used for different purposes. Since the second NAME column has so few rows, the names don't match up all the way through, which can be observed near the bottom of both the NAME columns.

我需要做的是有一个VLOOKUP可以在两列中标识名称，然后返回每个人(水平)(而不是跨列)访问的报告编号.它也需要身份证由于个人可以访问1-15个报告中的任何位置，所以报告数量众多，从第二个报告开始，依此类推.

What I need to do is have a VLOOKUP that identifies the name in both of the columns and then returns the report number that each individual has access to across rows (horizontally), not down the columns. It also needs to I.D. numerous reports since individuals have access to anywhere from 1-15 reports, starting at the second and so on after the previous has been extracted.

理想情况下，它看起来像这样:

Ideally it would look something like this:

Name          Report #      Name       ex column  ex column  ex column
Mark Doe      ReportXXX    Mark Doe    ReportXXX  ReportAAA   ReportB

我没有列出"Mark Doe"有权访问的其他报告，这些报告在大约4000的长长列表中的某处，以及他的名字在第一个"NAME"列中重复多次，但在第二个唯一"列中名称列将是行将报表返回到的位置.

I didn't list the other reports "Mark Doe" has access to and these would be somewhere down the long list of ~4000, along with his name repeated multiple times in the first NAME column, but the second "unique" name column would be where it is returning the reports to, across rows.

推荐答案

我制作了一种使用其他三列的方法，该方法对我有用. 我使用了您上面提供的数据.

I made a method that uses additional three columns and it worked for me. I used the data you provided above.

在左侧添加三列.

• 在A2中输入=COUNTIFS($D$2:$D2,D2,$E$2:$E2,E2).
• 在B2中输入=SUMIFS($A$2:$A2,$A$2:$A2,1,$D$2:D2,D2).
在C2中的
• 中输入=D2&B2.

• in A2 enter =COUNTIFS($D$2:$D2,D2,$E$2:$E2,E2).
• in B2 enter =SUMIFS($A$2:$A2,$A$2:$A2,1,$D$2:D2,D2).
• in C2 enter =D2&B2.

现在将这些单元格复制到您所有的4000行中.

Now copy those cells to all your 4000 rows.

现在，在第二名"列旁边，将列标题从1编号添加到您认为可能是一个用户可以拥有的最大报告数(您说15).

Now next to the second name column add column headers numbered from 1 to as many reports you think can be the max one user can have (you said 15).

在我的情况下，现在在F2 =IFERROR(VLOOKUP($F2&G$1, your table array starting from column C as absolute/fixed ,3,FALSE),"")中输入vlookup.

Now enter this vlookup in F2 =IFERROR(VLOOKUP($F2&G$1, your table array starting from column C as absolute/fixed ,3,FALSE),"") in my case it was =IFERROR(VLOOKUP($F2&G$1,$C$2:$E$11,3,FALSE),"").

在具有数字标题的所有列中向下复制公式，并在第二列中将所有具有名称的行向下复制(160).

Copy the formula accross all columns with number headers and down all rows that have names in second name column (160).

您的工作表应类似于此图片

最好，让我知道它是否对您有用.

All the best let me know if it worked for you.

这篇关于Excel 2013 VLOOKUP()基于多个条件，检索了多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！