如何获取与左列名称相关的标题行数据（其数据不是空的）(How to get header row data related to left column name (whose data are not empty))

大家好！ 我的示例数据如下图所示。 所以，当我在J2单元格中选择Harun ，我的预期结果是xxx, zzz, ppp 。 我的逻辑得到结果，因为Harun在xxx和zzz和ppp列中具有价值。 用下面的数组公式可以做到这一点，但它不是动态的。 Row（索引函数的数组）在这里是硬编码的。 那么，我可以将它设置为动态，以便在J2单元格中选择任何名称时可以获取所有标题项吗？ 感谢你的帮助。

=INDEX($C$2:$G$2,,SMALL(IF($C$3:$G$3<>"",COLUMN($C$3:$G$3),""),ROWS($B$3:$B3))-2)

示例数据截图

只有excel配方才是优选的。 如果不可能，那么我们可以选择VBA。

Good day to all! My sample data looks like below screenshot. So, when I will select Harun in J2 cell then my expected result is xxx, zzz, ppp. My logic to get result as Harun has value in xxx and zzz and ppp column. With the following array formula I can do that but it is not dynamic. Row (array for index function) is hard-coded here. So, can we make it dynamic so that I can get the all header item when I select any name in J2 cell? Appreciate your help.

=INDEX($C$2:$G$2,,SMALL(IF($C$3:$G$3<>"",COLUMN($C$3:$G$3),""),ROWS($B$3:$B3))-2)

Sample Data Screenshot

Only excel formula is preferable. If not possible then we can go with VBA.

最满意答案

在J3细胞中

=IFERROR(INDEX($C$2:$G$2,,SMALL(IF(OFFSET($B$2,MATCH(J$2,$B$3:$B$8,0),1,,5)<>"",COLUMN(OFFSET($B$2,MATCH(J$2,$B$3:$B$8,0),1,,5))-2,""),ROWS($1:1))),"")

将公式向下拖动并向右拖动

In J3 Cell

=IFERROR(INDEX($C$2:$G$2,,SMALL(IF(OFFSET($B$2,MATCH(J$2,$B$3:$B$8,0),1,,5)<>"",COLUMN(OFFSET($B$2,MATCH(J$2,$B$3:$B$8,0),1,,5))-2,""),ROWS($1:1))),"")

Drag the formula down and right

更多推荐