根据搜索条件创建行名列表 [英] Create list of row names based on search criteria

问题描述

我有以下Excel电子表格:

I have the following Excel spreadsheet:

     A            B     C        D      E            F            G        H
1                 Q1     Q2      Q3     Q4   Search criteria:     60     Asset 2
2   Asset 1       15     85      90     70                               Asset 3
3   Asset 2       40     80      45     60                               Asset 3
4   Asset 3       30     60      55     60                               Asset 5                       
5   Asset 4       12     72      25     15
6   Asset 5       60     48      27     98
7 

在Cells A1:E6中，我的资产与季度Q1-Q4的资产不同.
在Column H中，我想要一个与Cell G1中的搜索条件匹配的所有资产的列表.
在这种情况下，搜索条件为60，可在Cells A1:E6中找到Assets 2，3和5的内容.

In Cells A1:E6 I have different assets with their performance from quarter Q1-Q4.
In Column H I want to have a list of all assets that match the search criteria in Cell G1.
In this case the search criteria is 60 which can be found in the Cells A1:E6 for the Assets 2, 3 and 5.

如您所见，搜索条件可能连续出现多个时间(例如Row 4中).因此，我希望该资产在列表中两次列出(例如Asset 3).

As you can see it can happen that the search criteria exists mutliple times in a row (like in Row 4). Therefore, I want that the asset is listed two times in the list (like Asset 3).

您对可以创建此列表的公式有任何想法吗?

Do you have any idea of a formula that could create this list?

推荐答案

在H1中尝试

=INDEX(A:A,AGGREGATE(15,7,ROW($B$2:$E$6)/($B$2:$E$6=$G$1),ROW(1:1)))

然后抄下来.

如果您使用的是旧版Excel，则可以使用阵列版本的SMALL来完成:

If you are using an older version of Excel this can be done with an array version of SMALL:

=INDEX(A:A,SMALL(IF($B$2:$E$6=$G$1,ROW($B$2:$E$6)),ROW(1:1)))

作为数组公式，退出编辑模式时需要使用Ctrl-Shift-Enter而不是Enter进行确认.

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

这篇关于根据搜索条件创建行名列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！