英文:
Get a list of multiple values in a lookup
问题
在我的Excel工作簿中，我有一个名为testers的工作表，其中有两列，显示了一列测试人员和一个第三个布尔列作为电子邮件发送标志。
- A测试人员电子邮件
- B测试人员姓名 - 与当前问题无关
- C电子邮件是否已发送 - 与当前问题无关
在第二个工作表allocations中，包含有关将小部件分配给哪些测试人员的信息：
- A小部件ID
- B分配给小部件的测试人员1的电子邮件（从
testers工作表的A列进行数据验证） - E分配给小部件的测试人员2的电子邮件（从
testers工作表的A列进行数据验证） - 列C-D和F-J与当前问题无关
如下所示，小部件990235已分配给Jack和Mack。
对于jack@company.com，我应该得到列表"990235, 990236, 990231"。
我理解VLOOKUP不足以解决问题，因为它会找到第一个匹配并停止。此外，我的理解（我可能是错误的）是VLOOKUP会匹配第一列，查找需要在其右边，这在这里不适用。
英文:
In my Excel workbook, I have a sheet testers with 2 columns depicting a list of testers and a third boolean column as email sent flag.
- A tester email
- B tester name - irrelevant for current issue
- C email has been sent or not - irrelevant for current issue
| A | B | C |
|---|---|---|
| name | emailed | |
jack@company.com |
Jack | 0 |
mack@company.com |
Mack | 0 |
iris@company.com |
Iris B | 0 |
A second sheet allocations has information on which widget has been assigned to which testers:
- A widget ID
- B email of tester 1 assigned to the widget (data validation from column A of
testerssheet) - E email of tester 2 assigned to the widget (data validation from column A of
testerssheet) - columns C-D and F-J are not relevant for the current issue.
As shown in the example below, widget 990235 has been assigned to Jack and Mack.
| A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|
| widget_id | email1 | email1thanked | rating1 | email2 | email2thanked | rating2 | actionsent | difference | average |
| 990235 | jack@company.com |
0 | mack@company.com |
0 | 0 | ||||
| 990236 | mack@company.com |
0 | jack@company.com |
0 | 0 | ||||
| 990231 | jack@company.com |
0 | iris@company.com |
0 | 0 | ||||
| 990197 | iris@company.com |
0 | mack@company.com |
0 | 0 | ||||
| 990003 | mack@company.com |
0 | iris@company.com |
0 | 0 |
I am trying (and failing) to figure out how to get a list of all widget_ids allocated to any particular tester.
e.g. for jack@company.com I should get the list "990235, 990236, 990231"
I understand that VLOOKUP is not sufficient as it would find the first match and stop. Besides, my understanding (I may be wrong) is that VLOOKUP matches the first column and the lookup needs to be to the right of it, which is not the case here.
答案1
得分: 3
将此公式放入testers工作表的单元格D2中：
=MAP(
A2:A4,
LAMBDA(φ, TEXTJOIN("，", , IF(allocations!B2:E6 = φ, allocations!A2:A6, "")))
)
根据需要调整范围。
编辑：假设allocations工作表中列A的条目是数字，以下是适用于每个工作表中列A的最后条目的版本：
=LET(
κ, MATCH(7^89, allocations!A:A),
MAP(
DROP(TOCOL(A:A, 1), 1),
LAMBDA(φ,
TEXTJOIN(
"，", ,
IF(allocations!B2:INDEX(allocations!E:E, κ) = φ,
allocations!A2:INDEX(allocations!A:A, κ), "")
)
)
)
)
英文:
Put this formula in cell D2 of the testers sheet:
=MAP(
A2:A4,
LAMBDA(φ, TEXTJOIN(", ", , IF(allocations!B2:E6 = φ, allocations!A2:A6, "")))
)
Adjust the ranges as required.
Edit: assuming the entries in column A of the allocations sheet are numeric, a version which will work up to the last column A entry in each sheet:
=LET(
κ,MATCH(7^89,allocations!A:A),
MAP(
DROP(TOCOL(A:A,1),1),
LAMBDA(φ,
TEXTJOIN(
",",,
IF(allocations!B2:INDEX(allocations!E:E,κ)=φ,
allocations!A2:INDEX(allocations!A:A,κ),"")
)
)
)
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论